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.
