I am trying to count the number of employees per time period for a schedule ranging from morning shift to overnight shift. Everything works until it tries to count for those past the midnight hours, ie: Y42-Y47. I've tried adding conditional statements to the formula but it screws up the other cells.
the formula I'm using is "=SUMPRODUCT(($B$2:$B$27<=V42)*(MOD($C$2:$C$27-$B$2:$B$27,1)>MOD(($C$2:$C$27-TIME(,,1))-V42,1)))"where B2-B27 is the start time, C2-C27 is end time and V42 is the beginning of the time period ie: (12:00AM-12:30AM) for which I'd need number of staff.
Thank you to all!