I am making a work schedule to know how many agents I have every 15 minutes. I have the formula =SUMPRODUCT(--( E3>=B$3:B$47),--(E3<C$3:C$47)) which works great as my start times are in B3, my end of shift times are in C3 and my 15 min intervals are in E3. I have 47 total lines of shifts. My problem is the 13 shifts that end at or after midnight. I have 4 shifts that end right at midnight and 9 shifts that end at 1a. I am getting 0’s for those lines.
I tried a different formula I found in a forum of:
=SUMPRODUCT(((B$3:B$47<=E8)+(C$3:C$47>E8)+(B$3:B$47>E$3)=3)+0)
I got a value of 9 for 12:00AM and a value of 13 for 12:15AM-1:00AM which are both wrong. I should’ve gotten a value 13 for 12AM and 9 for everything after.