Help calculating number of staff working per hour over a 24hr period

[jakedunn]

Hello,

I am attempting to calculate the total amount of staff we have working at any hour during a 24 hour period but with my current technique the code does not work for any shifts that approach or go over midnight.. The code needs to account for all kinds of shifts over a 24hr period.

I have a table in the 'Data' worksheet where I list staff names, start times and finish times respectively across cells C5, C6 and C7

Then on 'Sheet 1' I have a list from cell B6:B29 with hourly time increments and a list from cell C6:C29 where I want to display the total amount of staff working on that hour. I am currently using the below code in C6 for 00:00 which then drags down, but this only seems to work for shifts that do not approach or cross midnight - If a shift works over midnight then no count is recorded at all.

=COUNTIFS(Data!$D$5:$D$26,"<="&B6,Data!$E$5:$E$26,">="&B6+1/24)

If anyone has any ideas, suggestions or can resolve this issue I will be extremely grateful..

Thank you for your time.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Please help.. trouble counting total number of staff per hour over a 24hr period
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.