Utilization of room by hours of the day

sfogle

New Member
Joined
Nov 17, 2018
Messages
2
All,

I have a data of start time and end time that represent and engine start and stop date/time. I have 10 engines. they could run for minutes, hours, >24 hours, days etc.

I would like to sum the time running for all hours of the day 0-24hr. A bar chart that sums the amount of hours for each hour of the day. If the engine runs from 1:30-3:30 then add 0.5 hours to 1 o'clock hour, 1hour to 2 o'clock, 0.5 hours to 3 o'clock.

I am having trouble writing a formula successful do this...especially for instances that start a 10pm on one day and end 2am the next day.

Any one have example of how to do this.

Shaun.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi,

For example in cell C2, you can use =Mod(B2-A2,1) and format this cell with custom format [h]:mm:ss

B2 is holding our end day and time ... and A2 contains your start day and time ...

Hope this will help
 
Upvote 0
Thanks for the response James006. This returns the duration, but how do allocate the duration to a specific hour of the day.

example:
Start time: 11/17/2018 23:00
End time: 11/18/2018 1:00
Duration is 2 hours
I want to build a table similar to this.....so i graph on a bar chart to sum up all the start/stop times to illustrate when in the day a engine is running.

Hour of day: 0:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 8:00 19:00 20:00 21:00 22:00 23:00 0:00
Hours Active: 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1

This is a simple example...I will want to sum fractions of hour into this.

Shaun
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top