# 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