Excel help

[Jake]

I have a number in a cell that represents a number of hours, how can I change it so that Excel displays it in "Hours:Mins:Secs" rather than as a decimal number?

eg
I have a cell with "3.75" in it but I need it to display this cell as "3h 45m 00s" or just as "3:45:00" would do.


Any ideas?


Thanks

[Pixelfill]

You can change the cell format to be hh:mm.

Mike

[RedM]

Isn't there an option if Format Cell?

[Jake]

You can change the cell format to be hh:mm.

Mike That doesn't do what I'm after though.
Formatting the cell to HH:MM changes 3.75 into "03/01/1900 18:00:00"

[RedM]

Any good?

http://support.microsoft.com/kb/266691

[Pixelfill]

I haven't got Excel installed here, but that makes no sense. :blink:

Which version of Excel?

try [h]:mm - that shouldn't limit it to a 24 hour period and shouldn't display the calendar date either

Mike

[Jake]

That doesn't do what I'm after though.
Formatting the cell to HH:MM changes 3.75 into "03/01/1900 18:00:00"

I haven't got Excel installed here, but that makes no sense. :blink: I think it's displaying it as "3.75 Days after Midnight 01-Jan-1900"

Which version of Excel? Excel 2000

try [h]:mm - that shouldn't limit it to a 24 hour period and shouldn't display the calendar date eitherNope. [h]:mm changes 3.75 in to 90:00
Odd innit?

[Homer]

I don't beleive there is a way to do this using Format cells, it'll need a conversion or formula. Can't find one that works so far :(

[Jake]

3.75 /24 formatted to [h]:mm works great.

Thanks fellas

[Homer]

Any good Jake?

=TEXT(A1/24, "hh:mm.ss")

Edit Doh, too slow yet again!