DA 6 - Duty Roster

General
This is a basic duty roster. It is not meant to be all encompassing. It will work for most situations, given you understand how a duty roster works. One key thing to remember is that each roster should be maintained on a separate roster. So if you are running a Weekday and Weekend Staff Duty Roster you should have two files.

8Jan2014 Update – Unprotected File
I have received a couple of emails in the last month asking for the unprotected file. The spreadsheet is password protected so that you can copy/cut and paste as you want. Without the password protection, if you copy/cut and paste, the formulas would be changed to reflect incorrect cells. If you need the significantly change the formulas, please let me know what you would like and I will change and modify them. I will also then upload that new file so everyone can benefit from the changes you recommend. If you want to change the formulas for a one-off situation, please email me and I will send you an unprotected copy.

8Jan2014 Update – Dates are Changeable
I have received several emails over the years because user thought that the date row (row 9) cannot be changed to remove invalid dates. This assumption is inaccurate. The entire date row is editable by you without unlocking the spreadsheet.

Example 1: You are doing a WEEKDAY only roster. You are starting a new roster at the beginning of the year (in this case the year is 2014). Since 1Jan2014 is a holiday, it shouldn’t be include in this roster. Step 1: You enter 2Jan2014 in cell E9. Step 2: You notice that the third day (cell H9 and I9) is a weekend and therefore doesn’t belong on this roster. Step 3: You change the date in cell I9 to reflect the next correct date for this roster. In this case that would be 6Jan2014. Step 4: Repeat Step 2 and 3 to remove any non-weekday days from this roster. The end result would be that you change cell S9 to 13Jan2014 and AC9 to 20Jan2014. This would remove any non-weekday days from the roster, assuming there are no three or four day weekends in that time.

Example 2: You are doing a WEEKEND/HOLIDAY only roster. You are starting a new roster at the beginning of the year (in this case the year is 2014). Since 1Jan2014 is a holiday, and the first day of the year, it would be the first date on the roster. Step 1: You enter 1Jan2014 in cell E9. Step 2: You notice that the second day (cell F9 and G9) is a not a weekend/holiday and therefore doesn’t belong on this roster. Step 3: You change the date in cell G9 to reflect the next correct date for this roster. In this case that would be 4Jan2014. Step 4: Repeat Step 2 and 3 to remove any weekday days from this roster. The end result would be that you change cell K9 to 11Jan2014, cell O9 to 18Jan2014, cell S9 to 25Jan2014, cell W9 to 1Feb2014, cell AA9 to 8Feb2014, cell AE9 to 15Feb2014 and AI9 to 22Feb2014. This would remove any weekday days from the roster, assuming there are no three or four day weekends in that time.

Example 3: You are doing a SPECIAL roster for unevenly dated items. This may include something like BDE staff duty and you are in a company. In situations like this, the BN would simply give you specific dates that you are responsible for providing people for this duty. Those dates probably will not be consecutive. Even in this case you may still need to keep a separate weekday and weekend/holiday rosters. Step 1: Clear all dates and formulas from the date row (row 9). Step 2: Enter the dates of duty as assigned, starting in the first available column and working toward the right. Step 3: Calculate the person assigned to that duty on those dates. NOTE: the TO (Date) field in the top right corner reflects the ‘current’ maximum date reflected on this roster. Step 4: Repeat step 2 and 3 until the roster is completed.

File change
Changed the auto-highlighting formulas so that it references the highest, second highest, and third highest, regardless if there is a number skip between them. This change was based on increased knowledge from when the spreadsheet was first created.

File change
As part of working with different people with different situations, I found an error in the original file regarding the ‘TO (Date)’ field (cell AH8). It was referencing the wrong field of the date row (row 9). I corrected this error.

File change
I significantly changed the formulas that calculated the individuals current number. This new format is significantly easier on the computer and reflects increased knowledge from when the spreadsheet was first created.

File change
Left the ‘format rows’ function as unprotected so you can change the height of a row if needed.

Requirements
MS Excel 2000 (Part of MS Office 2000).

Reference
AR 220-45 Duty Rosters.

Download
To download click the link above and click SAVE AS. Save the file on your computer and have fun.