Solved

# How to calculate utilization by time of day

Posted on 2011-10-03
386 Views
1 Endorsement
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
Question by:StAbDh

LVL 6

Expert Comment

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

Author Comment

Thank you for the prompt response.  Please see the attached spreadsheet example.  I think this will help clarify my question / request.
Test.xlsx
0

LVL 8

Expert Comment

Please refer to the attachment for formula...
Formula---time.xlsx
0

LVL 8

Accepted Solution

Formula---time.xlsx
0

Author Closing Comment

Amazing!  Thank you
0

## Featured Post

### Suggested Solutions

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…