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
StAbDhAsked:
Who is Participating?
 
wchhCommented:
Please ignore previous attachment
Formula---time.xlsx
0
 
MetallimirkCommented:
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.
0
 
StAbDhAuthor Commented:
Thank you for the prompt response.  Please see the attached spreadsheet example.  I think this will help clarify my question / request.
Test.xlsx
0
 
wchhCommented:
Please refer to the attachment for formula...
Formula---time.xlsx
0
 
StAbDhAuthor Commented:
Amazing!  Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.