Duty Roster_Help

[vishal0589]

Hi friends,
Can you help in this condition?

I am working on a duty roster. I need a formula with following conditions:

Shifts:
6:00 AM To 2:00 PM
2:00 PM To 10:00 PM
10:00 PM To 6:00 AM
9:00 AM To 5:00 PM
8:00 AM To 8:00 PM
8:00 PM To 8:00 AM
and so on.


A worker can work upto 24 hrs a day.

Normal duty could be of 8hrs or 12 Hrs.

If staff works for normal duty(1 shift) i.e. 8 hrs, then duty will be termed as "P8" (e.g: 6:00 AM To 2:00 PM)

If staff opts for overtime (4 Hrs, Fixed) , total duty would be termed as "P12" (Normal+Overtime)

If staff works for two to three shifts continuously (i.e. P8+P8 or P8+P8+P8), then total duty would be termed as "P16"

IF staff works for shift in night in dayone and his continuous duty of second shift falls under second day i.e. 1st Shift 10:00 PM To 6:00 AM (Day-1) and second shift 6:00 AM To 2:00 PM (Day-2), this would not be treated as overtime and would be charged seperately i.e, P8 for 1st shift and P8 for 2nd Shift (NOT P16)


There are 1600 staffs works on 2 to 3 shifts daily. Data have to be entered on daily basis.

I have Bold the area where I need formula to automatically fill the duty i.e. P8,P12, or P16.​

[TABLE="width: 567"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Date
[/TD]
[TD]Post Code[/TD]
[TD]Shift[/TD]
[TD]Service No[/TD]
[TD]P8
[/TD]
[TD]P12[/TD]
[TD]P16[/TD]
[/TR]
[TR]
[TD="align: right"]10/09/2014[/TD]
[TD="align: right"]1[/TD]
[TD]9:00 AM To 5:00 PM
[/TD]
[TD="align: right"]2030[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10/09/2014[/TD]
[TD="align: right"]2[/TD]
[TD]6:00 AM To 2:00 PM[/TD]
[TD="align: right"]2030[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11/09/2014[/TD]
[TD="align: right"]3[/TD]
[TD]2:00 PM To 10:00 PM[/TD]
[TD="align: right"]2284
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/09/2014[/TD]
[TD="align: right"]4[/TD]
[TD]10:00 PM To 6:00 AM[/TD]
[TD="align: right"]2030[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]13/09/2014[/TD]
[TD="align: right"]5[/TD]
[TD]9:00 AM To 5:00 PM[/TD]
[TD="align: right"]2030[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10/09/2014[/TD]
[TD="align: right"]6[/TD]
[TD]8:00 AM To 8:00 PM[/TD]
[TD="align: right"]2030[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10/09/2014[/TD]
[TD="align: right"]7[/TD]
[TD]8:00 PM To 8:00 AM[/TD]
[TD="align: right"]2030[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10/09/2014[/TD]
[TD="align: right"]8[/TD]
[TD]9:00 AM To 5:00 PM[/TD]
[TD="align: right"]2284
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10/09/2014[/TD]
[TD="align: right"]9[/TD]
[TD]6:00 AM To 2:00 PM[/TD]
[TD="align: right"]2250[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10/09/2014[/TD]
[TD="align: right"]10[/TD]
[TD]2:00 PM To 10:00 PM[/TD]
[TD="align: right"]2030[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10/09/2014[/TD]
[TD="align: right"]11[/TD]
[TD]10:00 PM To 6:00 AM[/TD]
[TD="align: right"]2284[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Thanks in Advance,

Vishal Srivastava​

 

[vishal0589]

Hi JB,
Right now, There is no any option available as 8 hrs rules for 12 hrs shift.
e.g:
Ist 12hrs shift "8:00 AM To 8:00 PM" will be counted as 12 hrs shift i.e. P12
and
IInd 12hrs shift "8:00 PM To 8:00 AM" will also be counted as 12 hrs shift i.e. P12

Please accept my apology in making sample data as it is only clerical error.

I am eagerly waiting for your solution.

Happy Journey.

Thanks In Advance,
Vishal Srivastava

 

[vishal0589]

No Problem mrmmickle1,
First of all thanks for your valuable time.

You tried your best at your end. It means alot. I am also working on it to find a solution.

It would be highly appreciated, if you try again and not to give up.

Thanks again.

Vishal Srivastava

 

[Pup Denab]

https://www.dropbox.com/s/k9jcg42eq21mtkn/Duty_Roster_Sample(update).xlsx?dl=0


Hi vishal0589
Welcome to the forum, your data could do with a logical setup, but no matter, please check the uploaded sheet, hopefully this will be able to help

 

[vishal0589]

Hi Pup,
Thanks for your contribution,

But if you check conditions, then following points are still missing from your solution:
1) If a staff works for 3 shift continuously (Max-24Hrs):
--> 1st Shift would be treated as P8 (i.e. 8hrs)
--> 11nd Shift would be treated as P16 (i.e. 8hrs) and
--> 111rd Shift would also be treated as P16 (i.e. 8hrs)

2) If think you have misunderstood 3hrs rules:
e.g: (for 2 shifts)
---> If Gap between two shifts is not more than 3 hrs:

--> 1st Shift would be treated as P8 (i.e. 8hrs)
--> 11nd Shift would be treated as P16 (i.e. 8hrs) and

---> If Gap between two shifts is more than 3 hrs:

--> 1st Shift would be treated as P8 (i.e. 8hrs)
--> 11nd Shift would also be treated as P8 (i.e. 8hrs)


It would be feasible for you, if you read my post from first point.

Thanks in Advance.

Vishal Srivastava

 

[mrmmickle1]

If you add the shift hours to the end of the data in Column C as seen below and Sort the Data By Date Level 1 and Service No Level 2 then the correct "P Value" will display in Column E.

You will need to insert this function in a regular code module to make the formula work:

Function Nth_Occurrence(range_look[COLOR=#0000ff] As [/COLOR]Range, find_it [COLOR=#0000ff]As String[/COLOR], occurrence [COLOR=#0000ff]As Long[/COLOR], offset_row [COLOR=#0000ff]As Long[/COLOR], offset_col [COLOR=#0000ff]As Long[/COLOR])
[COLOR=#008000]'This Function was found on the OzGrid Website[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] lCount [COLOR=#0000ff]As Long[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] rFound [COLOR=#0000ff]As [/COLOR]Range

  [COLOR=#0000ff]  Set[/COLOR] rFound = range_look.Cells(1, 1)
       [COLOR=#0000ff] For[/COLOR] lCount = 1 [COLOR=#0000ff]To [/COLOR]occurrence
          [COLOR=#0000ff]  Set[/COLOR] rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
        [COLOR=#0000ff]Next[/COLOR] lCount
    Nth_Occurrence = rFound.Offset(offset_row, offset_col)

[COLOR=#0000ff]End Function[/COLOR]

See Below Formula. Change Ranges accordingly:

Excel 2010
	A	B	C	D	E
1	Date	Post Code	Shift	Service No	Count
2	10/9/2014	1	1:00 AM To 9:00 AM, 8	2030	P16
3	10/9/2014	1	9:00 AM To 5:00 PM, 8	2030	P16
4	10/9/2014	3	10:00 PM To 6:00 AM, 8	2035	P8
5	10/9/2014	4	6:00 AM To 2:00 PM, 8	2284	P16
6	10/9/2014	5	2:00 PM To 10:00 PM, 8	2284	P16
7	10/10/2014	5	2:00 PM To 6:00 PM, 4	2290	P8
8	10/10/2014	6	2:00 PM To 6:00 PM, 4	2290	P8
9	10/11/2014	7	10:00 PM To 6:00 AM, 8	2211	P12
10	10/11/2014	3	2:00 PM To 6:00 PM, 4	2211	P12
11	10/11/2014	3	10:00 PM To 6:00 AM, 8	2215	P8
Sheet1

This formula goes in E2. Copy Down for above results:

=IF(COUNTIFS(A:A,A2,D:D,D2)=1,"P"&RIGHT(C2,1),IF(COUNTIFS(A:A,A2,D:D,D2)=2,"P"&RIGHT(Nth_Occurrence($D$2:$D$11,D2,1,0,-1),1)+RIGHT(Nth_Occurrence($D$2:$D$11,D2,2,0,-1),1),IF(COUNTIFS(A:A,A2,D:D,D2)=3,"P"&RIGHT(Nth_Occurrence($D$2:$D$11,D2,1,0,-1),1)+RIGHT(Nth_Occurrence($D$2:$D$11,D2,2,0,-1),1),+RIGHT(Nth_Occurrence($D$2:$D$11,D2,3,0,-1),1))))

 

[vishal0589]

Hi mrmmickle1

But My problem still remains the same.

As for your example

[TABLE="width: 468"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]10/09/2014
[/TD]
[TD="align: right"]1[/TD]
[TD]1:00 AM To 9:00 AM, 8[/TD]
[TD="align: right"]2030[/TD]
[TD]P16[/TD]
[/TR]
[TR]
[TD="align: right"]10/09/2014
[/TD]
[TD="align: right"]1[/TD]
[TD]9:00 AM To 5:00 PM, 8[/TD]
[TD="align: right"]2030[/TD]
[TD]P16[/TD]
[/TR]
</tbody>[/TABLE]


First Shift would be termed as P8
and
Second Shift would be termed as P16

Please look into this problem.

Vishal Srivastava

 

[JB_Scotland]

Vishal,

Here is my solution: https://dl.dropboxusercontent.com/u/4522548/Shift%20Log.xlsx

I hope you find it useful. Help notes and methodology are on the "Help Notes" worksheet. Let me know if you have any problems with it.

Although only a few simple steps are required to process your data a simple VBA macro could automate the whole process. You could record a macro when processing some data and then use that macro in future to run new data.

Regards

JB

 

[vishal0589]

Thanks JB,
It looks you have covered all the conditions.

I will try it on real scenario to make final assessment.

Thanks once again for your valuable time.

 

[JB_Scotland]

Pleased to help.