Converting time values to decimal equivalents; how?
Posted by Jeff Williams on December 30, 2001 1:19 PM
Can anyone help? I am trying to find a way to make a cell return a time value as the decimal equivalent.
Example: The elapsed time between 1:00pm and 2:15pm is one hour and fifteen minutes. If cell A1 contains the value 1:00pm and cell A2 contains the value 2:15pm, and you subtract A1 from A2 using this formula (A2-A1)in cell A3, the value returned is 1:15.
How can I get A3 to return the elapsed time value as the decimal equivalent: 1.25?
Ultimately I want to use the A3 decimal value to calculate the number of "events" per hour. For instance, if 400 units were sold in the last hour and fifteen minutes, what is the "unit per hour" rate? If A4 contains the value "400" and my formula in A5 is (A4/A3), the result returned should be 320 units per hour. I can't seem to make this work right using the time value of 1:15 derived by the formula in A3, no matter which of the time formats I try.
Thanks in advance for any help. Merry New Year!
Posted by Dank on December 30, 2001 1:57 PM
In the example you use you can just use the formula =(A2-A1)*24 and format this as number with 2 decimal places. The reason you multiply by 24 is because Excel stores time as a numeric value with 1 equal to 1 day. 2:15PM - 1:00PM equals 1h:15m. If you format this as number with ten decimal places you'll get 0.0520833333
which is the proportion of a day represented by 1hour and 15 minutes (75/Number of minutes in day). Therefore, to convert it from days to hours you multiply by 24.
I hope this makes sense,
Daniel.
