Page 1 of 1

Duty Roster / Shifts Planner

Posted: Mon Dec 21, 2020 4:39 am
by admin
Sir, there are 4 Shifts Rotation 24/7 all year
Duration of one shift Duty is 12 hours in Day and night
For Every Shift, the Person will have the Following Format of work in Rotation
Day duty next day night duty than in morning will off on next day also OFF
The Purpose of making sheet :

1. To determine On which Date Day or night Duty i.e if I want to check on 7th Jan 2021 I will have off or day duty or night duty I will see that above sheet to make plans accordingly
2. To Determine Special Events Eid Ramadan my Schedule of work will I have Day duty or night, in Ramadan our Working ours will be the same
3. To Tell other departments that on those specific dates on Which Shift will have Day Duty or night Duty


MS Excel default Template
msshifts.png

MS Excel file after Enhancements
shifts.png
Cell Formula

=IF(OR(NOT(ISNUMBER(C5)),C5<Job4_StartDate),"",IF(MID(Job4_Pattern,MOD(C5-Job4_StartDate,LEN(Job4_Pattern))+1,1)=Job4_Shift1_Code,1,IF(MID(Job4_Pattern,MOD(C5-Job4_StartDate,LEN(Job4_Pattern))+1,1)=Job4_Shift2_Code,2,IF(MID(Job4_Pattern,MOD(C5-Job4_StartDate,LEN(Job4_Pattern))+1,1)=Job4_Shift3_Code,3,""))))

Conditional Formatting

=$C$7:$AM$10,$C$14:$AM$17,$C$21:$AM$24,$C$28:$AM$31,$C$35:$AM$38,$C$42:$AM$45,$C$49:$AM$52,$C$56:$AM$59,$C$63:$AM$66,$C$70:$AM$73,$C$77:$AM$80,$C$84:$AM$87

Overlapping Check Conditional Formatting

=OR(COUNTIF(C7:C10,1)>1,COUNTIF(C7:C10,2)>1,COUNTIF(C7:C10,3)>1)

Download Template to use with 4 Shifts.
Shift work calendar v1.1.zip
(45.92 KiB) Downloaded 2019 times

Youtube Video,

https://youtu.be/_DXTz0c1tr8