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 number of hours, such as 156.90 to 156:54 in excel 2003.

All of the conversions I found did this incorrectly. The number of hours shouldn't be changed, only the decimal.

share|improve this question
add comment

3 Answers

up vote 7 down vote accepted
=TRUNC(A1)&":"&REPT("0",2-LEN(ROUND(60*(A1-TRUNC(A1)),0)))&ROUND(60*(A1-TRUNC(A1)),0)

Where A1 is the decimal value.

share|improve this answer
    
Awesome. I knew I would have to do something like that! –  Austin Henley Jul 26 '12 at 20:46
    
5.12 is converted to 5:7, anyway to make it become 5:07? –  Austin Henley Jul 26 '12 at 20:53
    
I added zero padding to 2 places... this makes it a bit more complex. ;-) credit blog.mclaughlinsoftware.com/2009/03/04/lpad-in-excel –  Chris Nava Jul 26 '12 at 21:06
    
Very nice, I wish I could up vote this a few more times. –  Austin Henley Jul 26 '12 at 21:14
    
I did, in @AustinHenley 's behalf, because I agree –  datatoo Jul 26 '12 at 22:08
add comment

Be aware that dates and times in Excel are stored internally as an amount of days. You can use this fact to simplify conversions of time amounts.

If you have an amount of hours in A1 you can calculate an amount of days using =A1/24 then display it as hours and minutes using the custom number format [h]:mm. Follow these steps:

  1. In the desired cell, enter the formula =A1/24
  2. Right click on the cell and choose Format Cells.
  3. On the Number tab, choose the Custom category, then type in [h]:mm

For more information about custom formats, see Create or delete a custom number format and Number format codes (look at the last section "Displaying hours, minutes, and seconds"). The brackets [ ] in the format code tell Excel to treat the amount as a duration and not a time of day. If you leave them off 25 hours is displayed as 1:00 because Excel interprets it as "1:00 AM of the next day".

You can use this kind of method to convert other amounts of time (amounts of seconds, minutes, or hours) into display formats like [h]:mm, [m]:ss, [h]:mm:ss or so on. Just use a formula to convert the amount to days, then use a custom number format to display the way you want. For example, if A1 is an amount of seconds, you can use =A1/24/60/60 with format [m]:ss.0 to get minutes, seconds, and tenths of seconds.

I find this method easier to remember and re-create as needed. The disadvantage is if other people need to edit your spreadsheet and they change the number format or copy and Paste Special, Values to a new location, the values no longer make sense.

share|improve this answer
    
+1 for the number format. –  Excellll Jul 27 '12 at 13:45
add comment

The easiest way to do this is to use @Bavi_H 's method, but instead wrap the formula in a TEXT() function specifying the format.

=TEXT(A1/24,"[h]:mm")

That way you don't need to rely on setting and maintaining number formats for the cell. And even though the value is stored as text, it can still be used in calculations (e.g. =MINUTES(B1) will return 54, and =TEXT(2*B1,"[h]:mm") will return 313:48).

share|improve this answer
    
This is useful! (+1) One small caveat to be aware of: If you use the x:y text in further calculations, it looks like Excel will assume the text is hours:minutes. This works for Austin Henley's question, but could cause miscalculations if you combine x:y text with a minutes:seconds format time. –  Bavi_H Jul 31 '12 at 2:20
add comment

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.