Duty Roster_Help

vishal0589

Board Regular
Joined
May 13, 2014
Messages
63
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
 
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
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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
 
Upvote 0
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:

Code:
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
ABCDE
1DatePost CodeShiftService NoCount
210/9/201411:00 AM To 9:00 AM, 82030P16
310/9/201419:00 AM To 5:00 PM, 82030P16
410/9/2014310:00 PM To 6:00 AM, 82035P8
510/9/201446:00 AM To 2:00 PM, 82284P16
610/9/201452:00 PM To 10:00 PM, 82284P16
710/10/201452:00 PM To 6:00 PM, 42290P8
810/10/201462:00 PM To 6:00 PM, 42290P8
910/11/2014710:00 PM To 6:00 AM, 82211P12
1010/11/201432:00 PM To 6:00 PM, 42211P12
1110/11/2014310:00 PM To 6:00 AM, 82215P8
Sheet1

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

Code:
=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))))
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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
Back
Top