Forum Discussion

PatTTMbid's avatar
PatTTMbid
Copper Contributor
Jun 27, 2024

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

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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:

    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!

     

    Hope this will help you.

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

    This will help all forum participants.

Resources