Forum Discussion
Rota planner for staff
Hi working in transport. I have given myself a task to recreate rota for drivers some of my driver are working every other week 6 days (Monday to Saturday) Sunday off
Rest of them are working every 5 weeks Saturday with rest during the week some of them Tuesday, Wednesday or Thursday.
Question is how to create a rule for it in excel if for every tab I have month assign to it to populate the rule.
In perfect world I would have a rule in separate TAB to print off work schedule for each driver.
1 Reply
- NikolinoDEGold Contributor
To achieve this, you can set up a system where you define the rules for each driver on a separate tab and have these rules applied to monthly tabs automatically. Here's a step-by-step guide on how to set this up:
Step 1: Define the Rules for Each Driver
Create a separate sheet (let's call it "Rules") where you define the schedule rules for each driver. This sheet will include the following columns:
- Driver Name
- Schedule Type (e.g., "Every Other Week", "Every 5 Weeks")
- Specific Days Off
Example:
Driver Name
Schedule Type
Day Off (1)
Day Off (2)
Day Off (3)
Driver A
Every Other Week
Sunday
Driver B
Every 5 Weeks
Tuesday
Driver C
Every 5 Weeks
Wednesday
Step 2: Set Up the Monthly Tabs
For each month, create a tab (e.g., "January", "February", etc.). These tabs will include a calendar layout where the drivers' schedules will be populated.
Example Layout for January:
Date
Day
Driver A
Driver B
Driver C
1/1/2024
Monday
1/2/2024
Tuesday
...
...
...
...
...
1/31/2024
Wednesday
Step 3: Populate the Schedules
You will use formulas to populate the schedules in each monthly tab based on the rules defined in the "Rules" tab.
Step-by-Step Formula Setup:
- Identify the Current Week: Use the WEEKNUM function to determine the current week number.
=WEEKNUM(A2) ' Assuming A2 is the date
- Determine Driver's Working Days:
- For "Every Other Week" schedule, use MOD to determine if the week is on or off.
- For "Every 5 Weeks" schedule, use a similar logic with MOD.
Example Formula for Driver A (Every Other Week):
=IF(AND(TEXT(A2, "ddd") <> "Sun", MOD(WEEKNUM(A2), 2) = 0), "Work", "Off")
Example Formula for Driver B (Every 5 Weeks):
=IF(AND(TEXT(A2, "ddd") <> "Tue", MOD(WEEKNUM(A2)-1, 5) <> 0), "Work", "Off")
Step 4: Automate the Schedule Population
To automate this process across all drivers and months, you can use a combination of formulas and a macro to fill in the schedules based on the rules defined.
Example of Automating with VBA:
- Open the VBA Editor:
- Press Alt + F11.
- Insert a Module:
- Right-click on any of the items in the "VBAProject" pane.
- Select Insert > Module.
- Add VBA Code to Populate Schedules:
Vba Code is untested backup your file
Sub PopulateSchedules() Dim ws As Worksheet Dim rulesSheet As Worksheet Dim driver As Range Dim monthSheet As Worksheet Dim i As Integer, j As Integer Dim scheduleType As String Dim dayOff1 As String, dayOff2 As String, dayOff3 As String Set rulesSheet = ThisWorkbook.Sheets("Rules") For Each monthSheet In ThisWorkbook.Sheets If monthSheet.Name <> "Rules" Then For Each driver In rulesSheet.Range("A2:A" & rulesSheet.Cells(Rows.Count, "A").End(xlUp).Row) scheduleType = driver.Offset(0, 1).Value dayOff1 = driver.Offset(0, 2).Value dayOff2 = driver.Offset(0, 3).Value dayOff3 = driver.Offset(0, 4).Value For i = 2 To monthSheet.Cells(Rows.Count, 1).End(xlUp).Row If scheduleType = "Every Other Week" Then If Application.WorksheetFunction.WeekNum(monthSheet.Cells(i, 1).Value) Mod 2 = 0 Then If Not monthSheet.Cells(i, 2).Value = dayOff1 Then monthSheet.Cells(i, driver.Column + 2).Value = "Work" Else monthSheet.Cells(i, driver.Column + 2).Value = "Off" End If Else monthSheet.Cells(i, driver.Column + 2).Value = "Off" End If ElseIf scheduleType = "Every 5 Weeks" Then If (Application.WorksheetFunction.WeekNum(monthSheet.Cells(i, 1).Value) - 1) Mod 5 <> 0 Then If Not monthSheet.Cells(i, 2).Value = dayOff1 Then monthSheet.Cells(i, driver.Column + 2).Value = "Work" Else monthSheet.Cells(i, driver.Column + 2).Value = "Off" End If Else monthSheet.Cells(i, driver.Column + 2).Value = "Off" End If End If Next i Next driver End If Next monthSheet End Sub
Run the Macro:
- Close the VBA editor.
- Press Alt + F8 to open the Macro dialog box.
- Select PopulateSchedules and click Run.
Summary
By setting up the rules in a dedicated sheet and using formulas or VBA to automate the population of the schedules, you can efficiently create a dynamic rota planner for your drivers. This system ensures that the schedules are updated according to the defined rules and can be easily printed or shared.
The text, steps and code were created with the help of AI. Consider checking important information.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.