Shift Wise Productivity Data for Shift Allowance

rams1981

New Member
Joined
Dec 30, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.

JobNodateSubmitopTranscribedateTranscribelinesTranscribeopProof1dateProof1linesProof1opProof2dateProof2linesProof2opProof3dateProof3linesProof3ukqaukqaproofdatelinesUkqadateComplete
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​
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcel Message Board.
Please Upload your file and Desired Result with XL2BB ADDIN or upload at free hosting site e.g www.dropbox.com and input link here.

for time at two column it is better to use same format ( both GMT or Both IST).
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
141
Office Version
  1. 2016
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,123,265
Messages
5,600,605
Members
414,393
Latest member
Vignesh Mechz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top