Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

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

=A1/24

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 at 12:33

try

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

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.

=TEXT((INT(A1)/1440+MOD(A1,1)/1440)*60,"hh:mm:ss")
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
1  
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)
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

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

the answer will be 10:34:48

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.