Duty Roster / Shifts Planner

Here MS Excel related topics will be posted.
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

Duty Roster / Shifts Planner

Post 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 1875 times

Youtube Video,

https://youtu.be/_DXTz0c1tr8
Malik Sikandar Hayat
admin@erpstuff.com
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest