Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:


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

share|improve this question

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


enter image description here

share|improve this answer
Yep, time values in Excel are stored internally as days and fractions of a day. – Daniel R Hicks May 8 '14 at 12:33
That's useful, but the reader should note that this a time value in a specific date. Not an absolute time value. – Ahmed Khalaf Dec 1 '15 at 12:02



enter image description here

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

share|improve this answer
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:

share|improve this answer
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


the answer will be 10:34:48

share|improve this answer

You must log in to answer this question.

protected by Community Aug 17 '15 at 17:26

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged .