I’m doing a time control sheet. I have the following data sheet:
From this data I want to extract data for a single agent by day of the week. I wanted an alternative to the array formula, which has the following limitations:
1- The array formula I used doesn’t work beyond the 1500 rows, my row extended around the 10.000.
2- Since some agents shift end on the next day. The formula can’t identify the last logout for the shift.
Book1 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | |||
1 | AgentID | LoginTime | LogoutTime | LogoutDate | Correlation ID+Date | ActualWorkedDay | TotalWorkedHours | ||||
2 | 17581 | 18:12:00 | 18:18:00 | 11/16/05 | 1758138672 | 11/16/05 | 0.10 | 3:00:00 | |||
3 | 17581 | 19:00:00 | 1:00:00 | 11/16/05 | 1758138671 | 11/15/05 | 6.00 | ||||
4 | 17581 | 9:59:00 | 11:28:00 | 11/17/05 | 1758138673 | 11/17/05 | 1.48 | ||||
5 | 17581 | 13:15:00 | 22:00:00 | 11/17/05 | 1758138673 | 11/17/05 | 8.75 | ||||
6 | 18423 | 13:05:00 | 17:18:00 | 11/16/05 | 1842338672 | 11/16/05 | 4.22 | ||||
7 | 18423 | 17:59:00 | 0:00:00 | 11/16/05 | 1842338671 | 11/15/05 | 6.02 | ||||
8 | |||||||||||
9 | |||||||||||
10 | |||||||||||
11 | AgentID | 17581 | |||||||||
12 | |||||||||||
13 | 11/16/2005 | 11/17/2005 | |||||||||
14 | Wednesday | Thursday | |||||||||
15 | Log | In | Out | In | Out | ||||||
16 | Actual | 18:12:00 | 18:18:00 | 9:59:00 | 22:00:00 | ||||||
Sheet1 |
From this data I want to extract data for a single agent by day of the week. I wanted an alternative to the array formula, which has the following limitations:
1- The array formula I used doesn’t work beyond the 1500 rows, my row extended around the 10.000.
2- Since some agents shift end on the next day. The formula can’t identify the last logout for the shift.