Shift Wise Productivity Data for Shift Allowance

[rams1981]

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.

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​

 

[maabadi]

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]

A similar problem with 3 shifts has been solved here:

Calculate Response time using different working hours during the week and excluding weekends / bankholidays

Hello I need a formula where it calculates Response Time considering business hours Mon - Thu 9am till 9pm and Fri 9am-5pm. In addition Bankholidays and Weekends should be excluded. Ticket Received Date is shown in Column B , Call Date is shown in Column C. Formula for Response Time for...

www.mrexcel.com