[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|
This question generated 29 answers. To proceed to the answers, click here.
This thread is current as of September 10, 2014.