Excel: Duty Roster_Help


[LEFT]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.[/LEFT]

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
[LEFT]Thanks in Advance, Vishal Srivastava[/LEFT]


This question generated 29 answers. To proceed to the answers, click here.

This thread is current as of September 10, 2014.


For more resources for Microsoft Excel