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

Date	Post Code	Shift	Service No	P8	P12	P16
10/09/2014	1	9:00 AM To 5:00 PM	2030
10/09/2014	2	6:00 AM To 2:00 PM	2030
11/09/2014	3	2:00 PM To 10:00 PM	2284
12/09/2014	4	10:00 PM To 6:00 AM	2030
13/09/2014	5	9:00 AM To 5:00 PM	2030
10/09/2014	6	8:00 AM To 8:00 PM	2030
10/09/2014	7	8:00 PM To 8:00 AM	2030
10/09/2014	8	9:00 AM To 5:00 PM	2284
10/09/2014	9	6:00 AM To 2:00 PM	2250
10/09/2014	10	2:00 PM To 10:00 PM	2030
10/09/2014	11	10:00 PM To 6:00 AM	2284

<colgroup><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

Thanks in Advance,

Vishal Srivastava​

 

[vishal0589]

Thanks JB,

Yes 12 hour entries would also be considered a two separate entries.

But
Rules you understood is a different one:
1) If a worker did 8 + 8 + 8 back to back shifts then P8 Column would show One Entry for 1st Shift and P16 column would show TWO entries of 8 hours (P16) for other two shift line that meets the rules.
viz.
Ist shift: P8
IInd Shift: P16
IIIrd Shift: P16


2) If a worker did 8 one day (overnight) straight into another 8 with no break the P8 column would show two entries of 8 hours.: (True)

3) If a worker did 8 + 4 back to back the P12 column would show one entry of 8 hours and one of 4 hours. (You could ignore this rule, as it is rare and overtime is now mostly fixed for 8 hrs shift; It means Point-1 appliesl; Please note 12 Hours normal shift available, where staff works for 12 hrs normal duty and P12 column would show the same).

4) Gap rule of 3 hours should also be considered.(As stated previously)


Vishal Srivastava

 

[JB_Scotland]

Ok got it.

 

[mrmmickle1]

What about something like this? :

Excel 2012
	A	B	C	D	E	F
1	Date	Post Code	Shift	Service No	Count	Designation
2	10/9/2014	1	1:00 AM To 9:00 AM	2030	3	P24
3	10/9/2014	1	9:00 AM To 5:00 PM	2030	3	P24
4	10/9/2014	3	10:00 PM To 6:00 AM	2030	3	P24
5	10/9/2014	4	6:00 AM To 2:00 PM	2284	2	P16
6	10/9/2014	5	2:00 PM To 10:00 PM	2284	2	P16
Sheet1

Cell Formulas
Range	Formula
E2	=COUNTIFS($A$2:$A$6,A2,$D$2:$D$6,D2)
E3	=COUNTIFS($A$2:$A$6,A3,$D$2:$D$6,D3)
E4	=COUNTIFS($A$2:$A$6,A4,$D$2:$D$6,D4)
E5	=COUNTIFS($A$2:$A$6,A5,$D$2:$D$6,D5)
E6	=COUNTIFS($A$2:$A$6,A6,$D$2:$D$6,D6)
F2	=IF(COUNTIFS($A$2:$A$6,A2,$D$2:$D$6,D2)=1,"P8",IF(COUNTIFS($A$2:$A$6,A2,$D$2:$D$6,D2)=2,"P16",IF(COUNTIFS($A$2:$A$6,A2,$D$2:$D$6,D2)=3,"P24","Not Available")))
F3	=IF(COUNTIFS($A$2:$A$6,A3,$D$2:$D$6,D3)=1,"P8",IF(COUNTIFS($A$2:$A$6,A3,$D$2:$D$6,D3)=2,"P16",IF(COUNTIFS($A$2:$A$6,A3,$D$2:$D$6,D3)=3,"P24","Not Available")))
F4	=IF(COUNTIFS($A$2:$A$6,A4,$D$2:$D$6,D4)=1,"P8",IF(COUNTIFS($A$2:$A$6,A4,$D$2:$D$6,D4)=2,"P16",IF(COUNTIFS($A$2:$A$6,A4,$D$2:$D$6,D4)=3,"P24","Not Available")))
F5	=IF(COUNTIFS($A$2:$A$6,A5,$D$2:$D$6,D5)=1,"P8",IF(COUNTIFS($A$2:$A$6,A5,$D$2:$D$6,D5)=2,"P16",IF(COUNTIFS($A$2:$A$6,A5,$D$2:$D$6,D5)=3,"P24","Not Available")))
F6	=IF(COUNTIFS($A$2:$A$6,A6,$D$2:$D$6,D6)=1,"P8",IF(COUNTIFS($A$2:$A$6,A6,$D$2:$D$6,D6)=2,"P16",IF(COUNTIFS($A$2:$A$6,A6,$D$2:$D$6,D6)=3,"P24","Not Available")))

It counts how many times a specific Date and Service No Occur. Then based on if it is Either 1 Occurence, 2 occurences or 3 occurences it will then assign a designation. Another option may be to include a number in the shift column. IE: 2:00 PM To 10:00 PM, 8 . This way you can use this number to tally the shift hours with a formula like this. Depending on which version of Excel you have, Excel may not recognize this as a number so you may have to use the formula containing NUMBERVALUE(I think this is new to Excel 2013) but, both formulas work for me (Using Excel 2013):

Excel 2012
	A	B
1	Shift	Tally Example
2	1:00 AM To 9:00 AM, 8	16
3	9:00 AM To 5:00 PM, 8	16
Sheet1

Cell Formulas
Range	Formula
B2	=RIGHT(A2,1) + RIGHT(A3, 1)
B3	=NUMBERVALUE(RIGHT(A2,1)) + NUMBERVALUE(RIGHT(A3, 1))

 

[JB_Scotland]

I have a solution for you but first I would like to test it fully with real data. Could you send me a link?

In the solution I have extra columns for sorting and calculation:
Worker Shift Start: Worker's service number combined with date and start time. (Used for data sort by worker in time sequence)
Worker Shift Finish: Worker's service number combined with date and finish time. (Used for calculating shift duration)
Shift Duration: Period in hours Worker Shift Finish - Worker Shift Start
Rest Period: The period in hours from the last shift worked by that worker.

P8 Column: Shows hours (8) if shift is not overtime
P12 Column: Shows hours (4 or 12) if shift is 4 hours overtime after an 8 hour shift or 12 for those workers on 12 hour shifts.
P16 Column: Shows hours (8 or 16) for overtime worked in consecutive shifts with maximum break between shifts of 3 hours all in one calendar day.

I hope I have interpreted you requirements correctly.

 

[vishal0589]

Hi mrmmickle1

Thanks for solution,

But I am not able to understand shift gap. Your formula works with Date and Service no and makes count. But where is the conditions where 3 hrs gap have to considered.
It has not recognised 12 hrs shift also.

Sorry If I have misinterpreted your solution.

Thanks in Advance.

 

[vishal0589]

Thanks JB,
I am glad that you have found the solution. I have made the sample data for 2 days as It is not possible for me to share actual data. It is just similar with real data with randomised values.

Please find the link for the same:
Wikisend: free file sharing service
or
TinyUpload.com - best file hosting solution, with no limits, totaly free

Kindly do the needful.

Waiting for your positive response.

Thanks in Advance.

 

[JB_Scotland]

Got the data, thanks.

Do you want your columns P8, P12 and P16 in integer (4, 8, 12, 16) or time format [h]:mm (4:00, 8:00, 12:00, 16:00)?

John Burdett

 

[mrmmickle1]

Please disregard this post. I thought I had a solution but the below formula failed in a few instances when data was set up like this:

Excel 2010
	A	B	C	D	E	F
1	Date	Post Code	Shift	Service No	Count	Designation
2	10/9/2014	1	1:00 AM To 9:00 AM, 8	2030	3	P20
3	10/9/2014	1	9:00 AM To 5:00 PM, 8	2030	3	P20
Sheet1

=IF(COUNTIFS(A:A,A2,D:D,D2)=1, "P" &RIGHT(C2,1),IF(COUNTIFS(A:A,A2,D:D,D2)=2, "P" &RIGHT(C2,1) + RIGHT(C3, 1),IF(COUNTIFS(A:A,A2,D:D,D2)=3,"P" &RIGHT(C2,1) + RIGHT(C3, 1) + RIGHT(C4, 1),"Not Available")))

Maybe someone is capable of extending this formula to a workable solution.....

 

[JB_Scotland]

Vishal

Your sample data set has some workers with a 12 hour shift followed immediately by another 12 hour one, e.g. "8:00 AM To 8:00 PM" then "8:00 PM to 8:00 AM" with no rest between. Should these all be recorded in the P12 column or any in the P16 column?

If the second 12 hours goes in the P16 column does the maximum 3 hours between shifts also apply for it to count as P16 time or does it immediately have to follow the last 12 hour shift to count.

Also there are a few data lines in your sample data with shift overlap where one worker is starting a new shift before the last one is finished or a duplicate shift on the same day. Also two of the shift entries are in a slightly different format "9:30AM To 5:30PM" with no gap between the time and the "AM" or "PM". These minor data errors perhaps which only occur in sample data only are not a problem for me and my spreadsheet solution should highlight them.

I am away tomorrow for a week so you may not get the solution until after I'm back. Enjoying working on it though.

JB

 

[vishal0589]

Hi JB,
In column P8,P12, and P16, it should show only P8,P12 and P16 respectively.

Thanks