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