How to Calculate How Long Between Times
I have access to a shared conference room. Wanting to make a report on how often the conference room is being utilized. I coded to extract the list of calendar events and event start and end dates. The problem is that does not tell me how long the room was being used. You can link to a sample spreadsheet to try to calculate the amount of time.
Do Not Subtract
It would sure be nice if it was as simple as subtracting the start time from the end time. However, this will give you some funky results that are not what you intended.
What is the Hour?
The way to start calculating is to create a formula that identifies what the hour is in the date. In a new cell, start with an equals sign and type =HOUR( and then click on the cell with the start date. Press enter. This will return the hour for the date (in military time.)
=HOUR(A2)
Want More Help with This? Become a Premium Member
Subtract
Now that you know the hour for the start and end time you can subtract to find the number of hours. In another column, you can subtract the end hour from the start hour to determine the number of hours.
Tip, you can combine this all into one formula instead of 3 different cells.
=HOUR(B2)-HOUR(A2)
Minutes
If only it was that easy. If all meetings started on the hour and ended on the hour then this method would work. Using =MINUTE() will return the minute mark, NOT the number of minutes.
=MINUTE(A2)
Let’s say a meeting starts at 9:15am. Then =HOUR(A2) will return 9 and =MINUTE(A2) will return 15. This is not 15 minutes, in fact, it is 15 minutes lost. The number of minutes is 60 minus the start minutes. The end minutes will indeed be how many minutes into that hour that you did meet.
=60 + MINUTES(B2) – MINUTES(A2)
Use All Minutes
Convert the number of hours so you can add the minutes.
=(HOUR(B2)-HOUR(A2))*60
Combine It All Together
To do this in one swoop, write a single formula. The problem with combining them is that when you subtract the hours you are double counting the minutes from the first hour. So you will need to subtract 60 minutes from the hours since the first hour is accounted for in your minutes calculation.
=(HOUR(B2)-HOUR(A2))*60 – 60 + 60 + MINUTES(B2) – MINUTES(A2)
Simplify this further and realize that subtracting 60 and adding 60 undoes each other and is thus superfluous.
=(HOUR(B2)-HOUR(A2))*60 + MINUTES(B2) – MINUTES(A2)
Convert to Hours
This calculation gives you the number of minutes rather than the number of hours. Divide by 60 to convert to hours.
=((HOUR(B2)-HOUR(A2))*60 + MINUTES(B2) – MINUTES(A2))/60
