Calculating Number of Employees Working at Specific Time Period

JAS7756

New Member
Joined
Apr 25, 2019
Messages
1
Hoping someone can help me with calculating the number of employees working during 15 minute time periods over 24 hours for a 7 day period. Currently I can calculate the number of employees working using the following formula: =COUNTIFS('Perm Sched Format'!I:I,"<="&A4,'Perm Sched Format'!J:J,">="&B4)

Where A4 and B4 are the start and end times (in this case 00:00 and 00:14) and the I:I and J:J column references are looking at the start and end times of the shift.

The formula works great except when some works through midnight (example: start at 11pm and end at 7am). I have been trying to do a nested COUNTIFS formula to look at the prior days start/end times but it doesn't seem to work.

Any help would be greatly appreciated!!

PS: I looked at several other threads but they didn't touch on the working past midnight and multiple days at once.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Unless all the employees are working the same schedule every day, you will need to include both the day and the time in the data for their schedules. The example you gave only talks about the time.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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