I’m trying to count employees per hour but the problem I have is that we have people clocking in and staying past 12AM.
Current Formula:
=sum(((A2>=$B$2:$B$32)*(A2<=$C$2:$C$32)))
A2 is the time of day so 12am,A3 is 1am so on and so forth
B column is the start times of the employee and C column is the end time.
The problem is when I get to late evenings I have employees clocking in at 4pm, 5pm,6pm etc….and clocking out 12am, 1am, or later resulting in them not being counted in the later evening times because their clock out times land outside of the formula. The middle of the day works perfectly though.
Does anyone know how to work around this?
Current Formula:
=sum(((A2>=$B$2:$B$32)*(A2<=$C$2:$C$32)))
A2 is the time of day so 12am,A3 is 1am so on and so forth
B column is the start times of the employee and C column is the end time.
The problem is when I get to late evenings I have employees clocking in at 4pm, 5pm,6pm etc….and clocking out 12am, 1am, or later resulting in them not being counted in the later evening times because their clock out times land outside of the formula. The middle of the day works perfectly though.
Does anyone know how to work around this?
