Excel 2003 - Time Formats

[IJ]

Hello,

I am trying to create a worksheet that will calculate how much money one has
earned according to the total number of hours worked. I have tried
formatting my "Time" column using the following format options:

Category Type

Time 13:30:55

Time 37:30:55

Custom h:mm:ss

Custom [h]:mm:ss

Custom h:mm:ss;@

Custom [h]:mm:ss;@

But, I am having problems obtaining the formatting I want. First, my times
are all reading as "actual times", such as "05:59:59" reads as "5:59:59 AM",
or"23:59:59" reads as "11:59:59 PM" instead of just "23:59:59".

And second, once I go over 23:59:59, in my "time" column, such as
"24:00:03", it reverts back to "12:00:03 AM".

Does anyone know how I can format my "time" column so that it will not:

1.) Revert back if I exceed 23:59:59?

And

2.) So that my "times" column will not read as an "actual time", but rather as
the "time value" in actually enter, such as "42:55:23"?

I would greatly appreciate any assistance, information, or suggestions
anyone may have and I thank you for taking the time to read my question.

I hope you are having a nice day.


Sincerely,

IJ Johnson

Freelance Bookkeeper | Independent Contractor
www.ij-ic.freewebsitehosting.com

 

[Bernard Liengme]

Try [hh]:mm:ss
The double hh should do what you want
best wishes

 

[Bernard Liengme]

PS: you do know that is A1 has a values in hours ([hh]:mm:ss) and B1 has
value
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme in dollars (hourly rate)
then to compute earnings you need =A1*24*B1 ?
Excel stores time as a fraction of a day; the 24 converts it to actual
hours.
best wishes

 

[FSt1]

hi
i have the same problems and per chip person, there is no format that will
do what you want.
the format [dd]:hh:mm:ss will not roll over the day as the hour format
[hh]:mm:ss rolls over the hour.
but he did profide me with a workaround.
i had to create a second column and use this formula
=INT(AS3)&":"&TEXT(AS3-INT(AS3),"hh:mm:ss")
adjust cell references to fit your data.
in my worksheet, the sheet is set up to measure a consumption process that
goes over 30 days now and then. the times are undated with a macro (manually
becasue we can't connect excel to the process) and the column that holds the
real time is hidden. the column with the real time doesnt' roll over but the
column with the formula does. maybe not the best solution but so far, it's
fooled our engeneers. and the time the formula displays is as accurate at the
real time.

not sure if it will help but so far as i know, it's the only workaround
going right now.

Regards
FSt1

 

[Fred Smith]

1. Use a format of [hh]:mm:ss. The square brackets tell Excel not to roll
over at 24 hours.
2. Same answer as 1.

This will format the cell. If you are looking at the formula bar, times will
be displayed as per your Windows Regional Settings.

Regards,
Fred

 

[Joe User]

I have tried formatting my "Time" column using
the following format options: [....]
Custom [h]:mm:ss

Something seems off. That Custom format should have worked for you, unless
you want the hours to always have at least 2 digits (possible leading zero),
in which case you want [hh]:mm:ss.

"05:59:59" reads as "5:59:59 AM", or"23:59:59"
reads as "11:59:59 PM" instead of just "23:59:59".
And second, once I go over 23:59:59, in my "time"
column, such as "24:00:03", it reverts back to "12:00:03 AM".

If you are looking in the Formula Bar, you will always see AM/PM times and
sometimes even dates like 1/1/1900. And yes, that makes it difficult to edit
some constants.

But with the Custom format [h]:mm:ss, you should see 24:00:03 in the cell.

Are you simply looking in the wrong place (Formula Bar)?


----- original message -----

Hello,

I am trying to create a worksheet that will calculate how much money one has
earned according to the total number of hours worked. I have tried
formatting my "Time" column using the following format options:

Category Type

Time 13:30:55

Time 37:30:55

Custom h:mm:ss

Custom [h]:mm:ss

Custom h:mm:ss;@

Custom [h]:mm:ss;@

But, I am having problems obtaining the formatting I want. First, my times
are all reading as "actual times", such as "05:59:59" reads as "5:59:59 AM",
or"23:59:59" reads as "11:59:59 PM" instead of just "23:59:59".

And second, once I go over 23:59:59, in my "time" column, such as
"24:00:03", it reverts back to "12:00:03 AM".

Does anyone know how I can format my "time" column so that it will not:

1.) Revert back if I exceed 23:59:59?

And

2.) So that my "times" column will not read as an "actual time", but rather as
the "time value" in actually enter, such as "42:55:23"?

I would greatly appreciate any assistance, information, or suggestions
anyone may have and I thank you for taking the time to read my question.

I hope you are having a nice day.


Sincerely,

IJ Johnson

Freelance Bookkeeper | Independent Contractor
www.ij-ic.freewebsitehosting.com