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.

 

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.

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 either

Nope. [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!