Bound4Glory
New Member
- Joined
- Jun 21, 2020
- Messages
- 18
- Office Version
- 365
- Platform
- Windows
EE ID | NAME | Clock In | Clock Out | TOTAL | Start Time | End Time | Employees | |
481 | ALLEN | 18:00 | 21:00 | 03:00 | 0:00 | 0:59 | 0 | |
15407 | ANTHONY | 20:00 | 2:00 | 1:00 | 1:59 | 0 | ||
12553 | STEVE | 2:00 | 2:59 | 0 | ||||
14276 | MARK | 3:00 | 3:59 | 0 | ||||
13851 | BRANDY | 16:00 | 22:00 | 06:00 | 4:00 | 4:59 | 0 | |
13967 | BARRY | 15:00 | 21:00 | 06:00 | 5:00 | 5:59 | 0 | |
980 | SMITH | 9:00 | 15:00 | 06:00 | 6:00 | 6:59 | 0 | |
7:00 | 7:59 | 0 | ||||||
8:00 | 8:59 | 1 | ||||||
9:00 | 9:59 | 1 | ||||||
10:00 | 10:59 | 1 | ||||||
11:00 | 11:59 | 1 | ||||||
12:00 | 12:59 | 1 | ||||||
13:00 | 13:59 | 1 | ||||||
14:00 | 14:59 | 1 | ||||||
15:00 | 15:59 | 2 | ||||||
16:00 | 16:59 | 2 | ||||||
17:00 | 17:59 | 2 | ||||||
18:00 | 18:59 | 2 | ||||||
19:00 | 19:59 | 2 | ||||||
20:00 | 20:59 | 1 | ||||||
21:00 | 21:59 | 0 | ||||||
22:00 | 22:59 | 0 | ||||||
23:00 | 23:59 | 0 |
I'm using a Countifs formula to get the number of employees during each hour, however, if an employee is clocked in overnight, it won't work properly. Any suggestions on how I could solve this?
=COUNTIFS($C$3:$C$70,"<="&G23,$D$3:$D$70,">"&H23)
Thank you