Excel time formula

jnoham

Hi,

I'm trying to convert a decimal time into hours and minutes in excel.

eg: 4.333 should read 4hrs 20 mins or 4.20; 1.5 should be 1:30 or 1 hr 30mins

Is this possible or any suggested work arounds?

Thanks

Dacelonid

http://support.microsoft.com/kb/214122

Seems to display the values as you want

demijose

Use the Mod function mod(number,1). the result will be the decimal part of the number.
=mod(number,1)

eg mod(1.333,1)= 0.33

take this result and multiply by 60 this will give you the minute part

=0.33 * 60 = 19.8 round it off to 19

then take the result from the mod function and subtract from the original number you get the hours.
= 1 - mod(1.333,1)
= 1

concatenate the two result using & operator
= cell reference for hour & "hours" & cell reference for minutes & "min"

hope u get this

thanks

Victor

Excel stores times as days, so a value of 0.00 is midnight, 0.50 is midday, 1.00 is midnight one day later. 5.00 is midnight 5 days later! So if your units are anything other than days, you need to convert it to days and change the formatting

Dacelonid wrote: »

http://support.microsoft.com/kb/214122

Seems to display the values as you want

I would go with this, although if its 4.333 minutes I would do the formula as 4.333/24/60 - that is 24 hours by 60 minutes as 1440 isn't the best known or most memorable number.

jnoham

Hi what if I am trying to add up hours worked per week and the number is over 24hours.
I'm currently using a formula =text(cell/24, "h.mm") which is giving me the result I want except if the total is over 24hours worked.

Thanks

semajnayr

If I understand what you are trying to do correctly I find the easiest trick is to divide by 0.041666667.

A1 = Start time (formatted HH:MM and entered as 16:30 for example 4.30pm)
B1 = Finish time (formatted HH:MM and entered as 20:30 for example 8.30pm)
C1 = B1-A1 equals hours worked (formatted HH:MM or 4:00)

However if you want to create a cost for this four hours at say 10 euro an hour you must first divide 4:00 by 0.041666667 and this will convert it into the numerical value 4. You can then multiply by 10 or perform other mathematical functions.

So, if you are trying to add up a roster for 5 days worked you should first get a daily hours worked (never over 24 hours) then divide as above, then sum the 5 days.

Hope this helps

semajnayr

Just read again and you want to go from decimal to HH:MM so multiply instead of divide

jnoham

Where are you getting 0.041666667 from?

semajnayr

Experience I guess, i don't think it's in any official excel help files. however i use it regularly as a method of converting hours and minutes to decimal numbers. have you tried it?

jnoham

Hi - what I'm trying to do is add time worked

Example:
5.30 + 4.23 + 2.13 = 11.66

11.66 = 11 hrs 39 mins
using formula = text(cell/24, "h:mm")

BUT
if we use the following example:
15.92 + 18.88 + 3.18 = 37.98

using the above forumla we get the time as 13:58 which is incorrect - it should be 37 hrs 58 mins.

Any ideas?

Thanks

semajnayr

ah right

just change the formula you are using to

= text(cell/24, "[h]:mm")

forcing excel to count beyond 24 hours

jnoham

Excellent - I think that work - thanks for your help