Rota planner for staff

PatTTMbid 

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:

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:

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!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.