Link to home
Start Free TrialLog in
Avatar of StAbDh
StAbDh

asked on

How to calculate utilization by time of day

Hello.

I need help to calculate the utilization of a room by time of day with MS Excel.

For instance, an individual uses a room from 8:15 - 17:30.  How do I take this date range and show the utilization in one hour intervals from 00:00 to 23:00:

7:00-8:00 - 0 min
8:00-9:00 - 45 min
9:00-10:00 - 60 min
-
-
17:00-18:00 - 30 min

I need to perform this caclutation on hundreds of rooms with an automated formula or code.

Thank you
Avatar of Metallimirk
Metallimirk
Flag of United States of America image

Here is a suggestion:

Keep column A as your start time, format cell as Time (I use the 1:30 PM format)
Keep column B as your end time, format cell as Time
Set column C with a formula of   =SUM(B1-A1)   also format cell as Time

If you have an 8:00AM start and and 10:00AM end, column C will display the time as 2:00 hours
If you change the time format, or need to divide by hours (ie: 48 hours = 2 Days, etc) you can run additional math. I hope this leads you in the right track, or give me more samples with real times or a sample spreadsheet.

Take care.
Avatar of StAbDh
StAbDh

ASKER

Thank you for the prompt response.  Please see the attached spreadsheet example.  I think this will help clarify my question / request.
Test.xlsx
Please refer to the attachment for formula...
Formula---time.xlsx
ASKER CERTIFIED SOLUTION
Avatar of wchh
wchh

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of StAbDh

ASKER

Amazing!  Thank you