Issues with Time being converted to Date/Time format

[D]

Hello all -
I'm hoping someone can help with this. It appears to be a
common problem but I can't seem to find an answer in any of the posts
I've seen. Here is my problem....I'm trying to maintain a spreadsheet
with time values in this format hhhh:mm:ss however, excel
automatically converts this to mm/dd/yyyy hh:mm:ss AM/PM. How can I
simply keep the hhhh:mm:ss format? Here is an example of what I am
trying to accomplish.

I'm looking to keep this format in the cell (and be able to do
calculations) 2455:25:48


The cell shows this format but when I look in the top detail bar it
shows 4/11/1900 7:25:48 AM

 

[Niek Otten]

Don't bother; the value is right. The representation in the formula bar is the number of hours since Jan 1, 1900, but the
underlying value is correct.
If you want to calculate with the number of hours as a number (for example to multiply with an hourly rate); keep in mind that
time in Excel is stored as a fraction of a day, so to get a decimal number of hours multiply by 24.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello all -
| I'm hoping someone can help with this. It appears to be a
| common problem but I can't seem to find an answer in any of the posts
| I've seen. Here is my problem....I'm trying to maintain a spreadsheet
| with time values in this format hhhh:mm:ss however, excel
| automatically converts this to mm/dd/yyyy hh:mm:ss AM/PM. How can I
| simply keep the hhhh:mm:ss format? Here is an example of what I am
| trying to accomplish.
|
| I'm looking to keep this format in the cell (and be able to do
| calculations) 2455:25:48
|
|
| The cell shows this format but when I look in the top detail bar it
| shows 4/11/1900 7:25:48 AM
|

 

[D]

Don't bother; the value is right. The representation in the formula bar is the number of hours since Jan 1, 1900, but the
underlying value is correct.
If you want to calculate with the number of hours as a number (for example to multiply with an hourly rate); keep in mind that
time in Excel is stored as a fraction of a day, so to get a decimal number of hours multiply by 24.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hello all -
| I'm hoping someone can help with this. It appears to be a
| common problem but I can't seem to find an answer in any of the posts
| I've seen. Here is my problem....I'm trying to maintain a spreadsheet
| with time values in this format hhhh:mm:ss however, excel
| automatically converts this to mm/dd/yyyy hh:mm:ss AM/PM. How can I
| simply keep the hhhh:mm:ss format? Here is an example of what I am
| trying to accomplish.
|
| I'm looking to keep this format in the cell (and be able to do
| calculations) 2455:25:48
|
|
| The cell shows this format but when I look in the top detail bar it
| shows 4/11/1900 7:25:48 AM
|

Thank you