Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Format
My customer received a file with columns listing times that represent how
long it took to do something. Example: 88:56:55 (88 hours, 56 minutes, 55 seconds). The problem is that the actual formatting of the cell is a custom format of: [=0.0416666666666666][h]:mm:ss;[m]:ss (Yikes!) What I need is to have the cell actually contain: 88:56:55. I am able to do the formula: =text(A1,"mm:ss") to get the 56:55 to show up. But if I do: =text(A1,"hh:mm:ss") I get 16:56:55. (It's working on a 24 hour clock for hours I guess and so I don't get the entire 88 hours that I want.) Any ideas would be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Format
Try: =TEXT(A1,"[h]:mm:ss")
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Pam" wrote: My customer received a file with columns listing times that represent how long it took to do something. Example: 88:56:55 (88 hours, 56 minutes, 55 seconds). The problem is that the actual formatting of the cell is a custom format of: [=0.0416666666666666][h]:mm:ss;[m]:ss (Yikes!) What I need is to have the cell actually contain: 88:56:55. I am able to do the formula: =text(A1,"mm:ss") to get the 56:55 to show up. But if I do: =text(A1,"hh:mm:ss") I get 16:56:55. (It's working on a 24 hour clock for hours I guess and so I don't get the entire 88 hours that I want.) Any ideas would be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Format
The custom format should show the hours correctly: the [h] forces Excel to
ignore rolling over 24 hour periods I don't know why it is necessary to do this but change your formula to: =TEXT(A1,"[hh]:mm:ss") "Pam" wrote: My customer received a file with columns listing times that represent how long it took to do something. Example: 88:56:55 (88 hours, 56 minutes, 55 seconds). The problem is that the actual formatting of the cell is a custom format of: [=0.0416666666666666][h]:mm:ss;[m]:ss (Yikes!) What I need is to have the cell actually contain: 88:56:55. I am able to do the formula: =text(A1,"mm:ss") to get the 56:55 to show up. But if I do: =text(A1,"hh:mm:ss") I get 16:56:55. (It's working on a 24 hour clock for hours I guess and so I don't get the entire 88 hours that I want.) Any ideas would be greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Format
Awesome!!! Thanks to both of you!!!
"Toppers" wrote: The custom format should show the hours correctly: the [h] forces Excel to ignore rolling over 24 hour periods I don't know why it is necessary to do this but change your formula to: =TEXT(A1,"[hh]:mm:ss") "Pam" wrote: My customer received a file with columns listing times that represent how long it took to do something. Example: 88:56:55 (88 hours, 56 minutes, 55 seconds). The problem is that the actual formatting of the cell is a custom format of: [=0.0416666666666666][h]:mm:ss;[m]:ss (Yikes!) What I need is to have the cell actually contain: 88:56:55. I am able to do the formula: =text(A1,"mm:ss") to get the 56:55 to show up. But if I do: =text(A1,"hh:mm:ss") I get 16:56:55. (It's working on a 24 hour clock for hours I guess and so I don't get the entire 88 hours that I want.) Any ideas would be greatly appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Format
0.416666... is one hour (1 divided by 24).
So that formatting says if it's less than an hour just show the minutes and seconds. If it's an hour or more, show it as [h]:mm:ss (you got explanations why it's using [h]). If wanted your formula to show the same as thing as the format did, you could use: =text(a1,"[=0.0416666666666666][h]:mm:ss;[m]:ss") But that's the same answer you got a week ago in your other post. Pam wrote: My customer received a file with columns listing times that represent how long it took to do something. Example: 88:56:55 (88 hours, 56 minutes, 55 seconds). The problem is that the actual formatting of the cell is a custom format of: [=0.0416666666666666][h]:mm:ss;[m]:ss (Yikes!) What I need is to have the cell actually contain: 88:56:55. I am able to do the formula: =text(A1,"mm:ss") to get the 56:55 to show up. But if I do: =text(A1,"hh:mm:ss") I get 16:56:55. (It's working on a 24 hour clock for hours I guess and so I don't get the entire 88 hours that I want.) Any ideas would be greatly appreciated. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format numbers/time format | Excel Discussion (Misc queries) | |||
Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions | |||
How to format cells in Excel for time in format mm:ss.00 | Excel Worksheet Functions | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |