Rota planner for staff

Copper Contributor

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.

Re: Rota planner for staff

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:

1. Identify the Current Week: Use the WEEKNUM function to determine the current week number.

=WEEKNUM(A2)  ' Assuming A2 is the date

1. 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:

1. Open the VBA Editor:
• Press Alt + F11.
2. Insert a Module:
• Right-click on any of the items in the "VBAProject" pane.
• Select Insert > Module.
3. 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!