MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Convert hours and minutes to minutes


Posted by Laurie on August 28, 2001 10:38 AM

I am no expert in Excel formulas and after looking through as many plain language Excel books I can find, I beseech your help.
I have to keep count of how many accounts are input in the time recorded. From this input data I need it to show the input rate per hour.
I record the start time in cell 1, stop time in cell 2, count input in cell 3. Cell 4 is a formula =cell 2 - cell 1 to compute total time spent inputing. How can I make Cell 4 show the total time as minutes? (Example: Cell 1 = 8:32, Cell 2 = 9:54. Cell 4 = 1:22. I need Cell 4 to convert to the whole number 82) Once I have that whole number I can write a formula for cell 5 such as =cell 3/(cell 4/60) to show the input rate per hour. If Cell 3= 134 then this would result in a input rate of 98 per hour =134/(82/60).
Thank you for your help!
Laurie


Posted by Barrie Davidson on August 28, 2001 10:44 AM

Assuming start time is in cell A1 and stop time is in cell B1, this formula will calculate total minutes.

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

Regards,
Barrie

Posted by IML on August 28, 2001 11:04 AM

Or, you could use
=B1-A1
with a custom format of
[m]

good luck...

Posted by Mark W. on August 28, 2001 11:31 AM

If cell A1 and B1 contain 8:32 and 9:54 respectively
the use the formula...

=(B1-A1)*1440

Posted by IML on August 28, 2001 11:33 AM

Rate per hour

After a second read, lets say you still have start time in A1, stop time in B1 and quantity processed in C1
Your rate per hour should be:
=C1/(B1-A1)/24

with a general or number format. Excel stores time as fractions of days. Mark W. has been saying this forever and I'm just starting to believe him!