# Conversion of decimal hours to hours:minutes:seconds

I need to convert a number of decimal hours (example: 4.96) and have it read 4:57:36 (4hrs, 57min, 36sec) in an Excel 2003 spreadsheet. I have this formula already, but it doesn't show the seconds. It returns a result of 4:58.

Here's the formula:

``````=TRUNC(A1)&":"&REPT("0",2-LEN(ROUND(60*(A1-TRUNC(A1)),0)))&ROUND(60*(A1-TRUNC(A1)),0)
``````

What do I need to change to show the seconds too?

-

Can't you just divide by 24 and custom format the result cell as [h]:mm:ss?

`=A1/24`

-
Yep, time values in Excel are stored internally as days and fractions of a day. –  Daniel R Hicks May 8 '14 at 12:33

try

``````=(INT(A1)/1440+MOD(A1,1)/1440)*60
``````

Edit: if you insist on a text output (with all the negative implications that brings), just wrap the formula in a Text() function.

``````=TEXT((INT(A1)/1440+MOD(A1,1)/1440)*60,"hh:mm:ss")
``````
-
I think your solution is a lot cleaner than mine (that just built upon the TRUNC H:M example) but it does need the cell to be formatted as datetime, otherwise it shows a decimal. –  Dave C Mar 14 '13 at 23:41
If the data type is date/time, it should be stored as a number and formatted accordingly. Storing date/time as text that looks like date/time only makes matters more complicated, for example if you want to run calculations on the values (difference between two times, etc). The extra click for applying a cell format other than text is well worth it in the long run. I've added a text version above, though. –  teylyn Mar 15 '13 at 0:13
Thanks so much! This works great =(INT(A1)/1440+MOD(A1,1)/1440)*60 All I do is format the cell to display hh:mm:ss. Thanks again!! –  Mike Kam Mar 15 '13 at 0:21
@teylyn I absolutely agree fields /should/ be formatted correctly. And your solution (date/time or text) is much cleaner and more efficient. –  Dave C Mar 15 '13 at 0:32

Assuming your H:M is working correctly easiest way is to convert hour decimal to seconds and use MOD to check difference with division by 60.

So the formula would be:

``````=TRUNC(A1)&":"&REPT("0",2-LEN(ROUND(60*(A1-TRUNC(A1)),0)))&ROUND(60*(A1-TRUNC(A1)),0)&":"&REPT("0",2-LEN(ROUND(MOD(A1*60*60,60),0)))&ROUND(MOD(A1*60*60,60),0)
``````
-
I don't think this works, Dave - for the example given the formula returns 4:58:36, i.e. one minute too high - it does that whenever the seconds part is > 30 - you can do this much more simply - see my answer –  barry houdini Mar 15 '13 at 0:39

you can use the below formula,

if you want to convert the decimal number (A2) 10.58

`=TEXT(A2/24,"hh:mm:ss")`