converting decimal (hh.mm) in hh:mm format

How can i change hours and minutes (which are in decimal ) to hh:mm format.
File is attached for ref.

excel doesnt really like showing hours over 24. so it may be better to keep the times in that format.

when I convert 230.25 with...
=TIME(LEFT(B3,SEARCH(".",B3,1)-1)*1+(INT(B3/24)*24),RIGHT(B3,2)*1,0)
it gives me 14:25

Originally Posted by FDibbins

excel doesnt really like showing hours over 24. so it may be better to keep the times in that format.

when I convert 230.25 with...
=TIME(LEFT(B3,SEARCH(".",B3,1)-1)*1+(INT(B3/24)*24),RIGHT(B3,2)*1,0)
it gives me 14:25

sir ... 230.25 means ... There are 230 HOURS and 15 minutes.


so it should show 230:15 (hh:mm)
Hope now you are cleared.

Hello,

On C3, you can paste this formula

Please Login or Register  to view this content.

However, as FDibbins said, Excel will not show hh:mm over 24 hours, so this is just a rough way to force the number into text.

Edit: Aparently you can format it to actually look like hh:mm that is over 24 hours. See post #5 for details.

Originally Posted by FDibbins

excel doesnt really like showing hours over 24

That's not true.

Use this formula in C3

=B3/24

custom format as [h]:mm and you'll get 230:15

Wow, I have never known that before. Thanks daddylonglegs for the solution, that's a great one!