How to calculate utilization by time of day

Posted on 2011-10-03
1 Endorsement
Last Modified: 2012-08-14

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
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.

    Author Comment

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

    Expert Comment

    Please refer to the attachment for formula...
    LVL 8

    Accepted Solution

    Please ignore previous attachment

    Author Closing Comment

    Amazing!  Thank you

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    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…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now