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
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
ASKER
Thank you for the prompt response. Please see the attached spreadsheet example. I think this will help clarify my question / request.
Test.xlsx
Test.xlsx
Please refer to the attachment for formula...
Formula---time.xlsx
Formula---time.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Amazing! Thank you
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.