Hi,
We have users working on different shifts as per the slots mentioned below. To pay them shift allowance based on their daily productivity, we need to know their total daily productivity numbers during their shift hours. Please find attached the source data to be worked on to arrive at the required data. Each job will go through multiple levels processed by one single user at all levels or multiple users at different levels. Request your help in creating a template to arrive at the desired data. Thanks in advance.
6 am to 2 pm shift - First shift
2 pm to 10 pm shift - Second shift
4 pm to 12 am shift - Dusk Shift
10 pm to 6 am shift - Night Shift
PS: The date/time format in the attached sheet is in GMT but the shift timings is as per IST.
We have users working on different shifts as per the slots mentioned below. To pay them shift allowance based on their daily productivity, we need to know their total daily productivity numbers during their shift hours. Please find attached the source data to be worked on to arrive at the required data. Each job will go through multiple levels processed by one single user at all levels or multiple users at different levels. Request your help in creating a template to arrive at the desired data. Thanks in advance.
6 am to 2 pm shift - First shift
2 pm to 10 pm shift - Second shift
4 pm to 12 am shift - Dusk Shift
10 pm to 6 am shift - Night Shift
PS: The date/time format in the attached sheet is in GMT but the shift timings is as per IST.
JobNo | dateSubmit | opTranscribe | dateTranscribe | linesTranscribe | opProof1 | dateProof1 | linesProof1 | opProof2 | dateProof2 | linesProof2 | opProof3 | dateProof3 | linesProof3 | ukqa | ukqaproofdate | linesUkqa | dateComplete |
102117 | 14/12/2020 13:10 | Arok943 | 15/12/2020 04:00 | 18.877 | Arok943 | 15/12/2020 04:00 | 18.877 | Arok943 | 15/12/2020 04:00 | 18.877 | 15/12/2020 04:00 | ||||||
102132 | 14/12/2020 13:16 | Arok943 | 15/12/2020 04:02 | 10.523 | Arok943 | 15/12/2020 04:02 | 10.523 | Arok943 | 15/12/2020 04:02 | 10.523 | 15/12/2020 04:02 | ||||||
102158 | 14/12/2020 13:32 | Arok943 | 15/12/2020 04:06 | 12.477 | Arok943 | 15/12/2020 04:06 | 12.477 | Arok943 | 15/12/2020 04:06 | 12.477 | 15/12/2020 04:06 | ||||||
102197 | 14/12/2020 13:54 | Arok943 | 15/12/2020 04:09 | 16.677 | Arok943 | 15/12/2020 04:09 | 16.677 | Arok943 | 15/12/2020 04:09 | 16.677 | 15/12/2020 04:09 | ||||||
102215 | 14/12/2020 14:01 | Arok943 | 15/12/2020 04:12 | 6.492 | Arok943 | 15/12/2020 04:12 | 6.492 | Arok943 | 15/12/2020 04:12 | 6.492 | 15/12/2020 04:12 | ||||||
102236 | 14/12/2020 14:09 | Arok943 | 15/12/2020 04:14 | 11.846 | Arok943 | 15/12/2020 04:14 | 11.846 | Arok943 | 15/12/2020 04:14 | 11.846 | 15/12/2020 04:14 | ||||||
102256 | 14/12/2020 14:18 | Arok943 | 15/12/2020 04:18 | 13.8 | Arok943 | 15/12/2020 04:18 | 13.8 | Arok943 | 15/12/2020 04:18 | 13.8 | 15/12/2020 04:18 | ||||||
102305 | 14/12/2020 14:43 | Arok943 | 15/12/2020 04:22 | 16.861 | Arok943 | 15/12/2020 04:22 | 16.861 | Arok943 | 15/12/2020 04:22 | 16.861 | 15/12/2020 04:22 | ||||||
102346 | 14/12/2020 14:59 | Sabi1057 | 15/12/2020 03:00 | 10.846 | Sabi1057 | 15/12/2020 03:00 | 10.846 | Sabi1057 | 15/12/2020 03:00 | 10.846 | 15/12/2020 03:00 | ||||||
102393 | 14/12/2020 15:14 | Sabi1057 | 15/12/2020 05:26 | 10.385 | Sabi1057 | 15/12/2020 05:26 | 10.385 | Sabi1057 | 15/12/2020 05:26 | 10.385 | 15/12/2020 05:26 | ||||||
102477 | 14/12/2020 15:49 | Arok943 | 15/12/2020 05:26 | 22 | Arok943 | 15/12/2020 05:26 | 22 | Arok943 | 15/12/2020 05:26 | 22 | 15/12/2020 05:26 | ||||||
102526 | 14/12/2020 16:10 | Sabi1057 | 15/12/2020 05:45 | 33.046 | Sabi1057 | 15/12/2020 05:45 | 33.046 | Sabi1057 | 15/12/2020 05:45 | 33.046 | 15/12/2020 05:45 | ||||||
103286 | 15/12/2020 13:14 | Arok943 | 16/12/2020 03:07 | 16.477 | Arok943 | 16/12/2020 03:07 | 16.477 | Arok943 | 16/12/2020 03:07 | 16.477 | 16/12/2020 03:07 | ||||||
103342 | 15/12/2020 14:03 | Arok943 | 16/12/2020 04:41 | 25.846 | Arok943 | 16/12/2020 04:41 | 25.846 | Arok943 | 16/12/2020 04:41 | 25.846 | 16/12/2020 04:41 | ||||||
103374 | 15/12/2020 14:25 | Arok943 | 16/12/2020 04:47 | 19.969 | Arok943 | 16/12/2020 04:47 | 19.969 | Arok943 | 16/12/2020 04:47 | 19.969 | 16/12/2020 04:47 | ||||||
103414 | 15/12/2020 14:44 | Arok943 | 16/12/2020 04:55 | 28.323 | Arok943 | 16/12/2020 04:55 | 28.323 | Arok943 | 16/12/2020 04:55 | 28.323 | 16/12/2020 04:55 | ||||||
103490 | 15/12/2020 15:26 | Arok943 | 16/12/2020 05:33 | 36.677 | Arok943 | 16/12/2020 05:33 | 36.677 | Arok943 | 16/12/2020 05:33 | 36.677 | 16/12/2020 05:33 | ||||||
103873 | 16/12/2020 06:16 | Sabi1057 | 16/12/2020 06:56 | 36.846 | Sabi1057 | 16/12/2020 06:56 | 36.846 | Sabi1057 | 16/12/2020 06:56 | 36.846 | 16/12/2020 06:56 | ||||||
K05363992 | 15/12/2020 11:43 | Arok943 | 15/12/2020 13:29 | 25.385 | Arok943 | 15/12/2020 13:29 | 25.385 | Arok943 | 15/12/2020 13:29 | 25.385 | 15/12/2020 13:29 | ||||||
K05363993 | 15/12/2020 11:43 | Arok943 | 15/12/2020 13:35 | 38.354 | Arok943 | 15/12/2020 13:35 | 38.354 | Arok943 | 15/12/2020 13:35 | 38.354 | 15/12/2020 13:35 | ||||||
K05363994 | 15/12/2020 11:43 | Arok943 | 15/12/2020 13:41 | 19.862 | Arok943 | 15/12/2020 13:41 | 19.862 | Arok943 | 15/12/2020 13:41 | 19.862 | 15/12/2020 13:41 | ||||||
K05363996 | 15/12/2020 11:43 | Arok943 | 15/12/2020 13:42 | 5.862 | Arok943 | 15/12/2020 13:42 | 5.862 | Arok943 | 15/12/2020 13:42 | 5.862 | 15/12/2020 13:42 | ||||||
K05363997 | 15/12/2020 11:43 | Sabi1057 | 15/12/2020 15:20 | 6.616 | Sabi1057 | 15/12/2020 15:20 | 6.616 | Sabi1057 | 15/12/2020 15:20 | 6.616 | 15/12/2020 15:20 | ||||||
K05363998 | 15/12/2020 11:43 | Sabi1057 | 15/12/2020 15:26 | 16.123 | Sabi1057 | 15/12/2020 15:26 | 16.123 | Sabi1057 | 15/12/2020 15:26 | 16.123 | 15/12/2020 15:26 | ||||||
K05363999 | 15/12/2020 11:43 | Sabi1057 | 15/12/2020 15:30 | 8.492 | Sabi1057 | 15/12/2020 15:30 | 8.492 | Sabi1057 | 15/12/2020 15:30 | 8.492 | 15/12/2020 15:30 | ||||||
K05364000 | 15/12/2020 11:43 | Sabi1057 | 15/12/2020 15:37 | 21.4 | Sabi1057 | 15/12/2020 15:37 | 21.4 | Sabi1057 | 15/12/2020 15:37 | 21.4 | 15/12/2020 15:37 | ||||||
K05364001 | 15/12/2020 11:43 | Sabi1057 | 15/12/2020 15:52 | 39.847 | Sabi1057 | 15/12/2020 15:52 | 39.847 | Sabi1057 | 15/12/2020 15:52 | 39.847 | 15/12/2020 15:52 | ||||||
K05364002 | 15/12/2020 11:44 | Sabi1057 | 15/12/2020 16:04 | 22.185 | Arok943 | 16/12/2020 02:48 | 22.185 | Arok943 | 16/12/2020 02:48 | 22.185 | 16/12/2020 02:48 | ||||||
K05367696 | 17/12/2020 13:51 | Arok943 | 18/12/2020 04:11 | 13.4 | Arok943 | 18/12/2020 04:11 | 13.4 | Arok943 | 18/12/2020 04:11 | 13.4 | 18/12/2020 04:11 | ||||||
K05367697 | 17/12/2020 13:51 | Arok943 | 18/12/2020 04:07 | 15.631 | Arok943 | 18/12/2020 04:07 | 15.631 | Arok943 | 18/12/2020 04:07 | 15.631 | 18/12/2020 04:07 | ||||||
K05367698 | 17/12/2020 13:51 | Arok943 | 18/12/2020 02:57 | 10 | Arok943 | 18/12/2020 02:57 | 10 | Arok943 | 18/12/2020 02:57 | 10 | 18/12/2020 02:57 | ||||||
K05367699 | 17/12/2020 13:51 | Arok943 | 18/12/2020 02:13 | 5.431 | Arok943 | 18/12/2020 02:13 | 5.431 | Arok943 | 18/12/2020 02:13 | 5.431 | 18/12/2020 02:13 | ||||||
K05367700 | 17/12/2020 13:51 | Arok943 | 18/12/2020 03:14 | 31.077 | Arok943 | 18/12/2020 03:14 | 31.077 | Arok943 | 18/12/2020 03:14 | 31.077 | 18/12/2020 03:14 |