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

jakedunn

New Member
Joined
Mar 13, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Data.PNG
    Data.PNG
    22.6 KB · Views: 10
  • Sheet 1.PNG
    Sheet 1.PNG
    26 KB · Views: 10

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,567
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,181,081
Messages
5,927,970
Members
436,581
Latest member
Luceroso

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
Top