Time Format

[Guest]

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.

 

[Guest]

Try: =TEXT(A1,"[h]:mm:ss")

 

[Guest]

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")

 

[Guest]

Awesome!!! Thanks to both of you!!!

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")

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]

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.

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.