MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Adding Time formats together


Posted by AxisTangent on March 15, 2001 9:06 PM

I have a small consulting company that bills / minute. If I have a start time field, and end time field and the values are 8:15 and 10:30, I have subtracted one cell from the other to get 2:15 in the third cell. How can I convert 2:15 to 135 minutes? The third cell is C2, I have tried =HOUR(C2)*60+MINUTE(C2) and it returns a value 0:00. Any ideas?

AxisTangent


Posted by Dave Hawley on March 15, 2001 9:35 PM


Hi AxisTangent

Excel stores hours as portions of a day, with one being equal to 24hrs. So to convert to minutes use: =(C2*60)*24

But a better method IMO is to custom format your cell as: [mm]


Dave


OzGrid Business Applications

Posted by Aladin Akyurek on March 15, 2001 9:40 PM

=HOUR(B1-A1)*60+MINUTE(B1-A1)

A1 is the start time, B1 the end time. Format the cell of this formula as number. Watch out: the assumption here is that both times fall in the same day and times are not in AM/PM, but in 24 hour format.

Aladin

Posted by Mark W. on March 16, 2001 5:54 AM

A word of caution...

Axis, Dave's recommended solution using the [mm]
format doesn't change the internal representation
of the difference of your 2 times to minutes.
Using your example, if you were to add the
integer 10 (believing that you were adding 10
min.) to C2 (formatted as [mm]) you'd get
10.09375 instead of 145.