• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

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
1
StAbDh
Asked:
StAbDh
  • 2
  • 2
1 Solution
 
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
 
wchhCommented:
Please ignore previous attachment
Formula---time.xlsx
0
 
StAbDhAuthor Commented:
Amazing!  Thank you
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now