7 Day Shift Scheduling question

[cmiles]

Currently trying to find the best schedule for 7 (Day only) 12 hour shift with 70-80 employees and needing 30 to 40 people each day. If it's possible I'd like to keep the number of shifts per week per employee to 4. Any help would be greatly appreciated. Creativity is welcome. 3 days even better. If this doesn't work with my current staffing what should my overall manpower number be?

Thanks,

 

[Perpa]

cmiles,
I didn't see any responses to your post so I took a look...
Not knowing what format you wanted, I took a simple approach:
You have 70-80 employees... so I listed Employees by name ('Emp1', 'Emp2' and etc.) in 2 columns B and C (40 in each column), beginning in row 6.
The column headers are the Days of the Week ('Monday' to 'Sunday') in row 4. I put the start date of the week at the top LH corner of the sheet.


The simplest approach would be to alternate those names (40 each day) for the remainder of the week. That would give Emp1 to Emp40 4 shifts each, and Emp41 to Emp80 3 shifts each.
You may not have 40 employees assigned each day, so...
To see how many shifts each employee has during the week I created another list of those employees in a 10 cell x 21 cell grid so all emloyees would be visible on screen.
That grid is in the range J6: S27. As shown it has 80 employees with room below each to show the total number of shifts assigned.
After all the shifts are assigned in columns B to H, I used a macro to count the number of shifts each employee has for the week.
The macro relies on the employees being located as shown in the range J6: S27 to get that count. If you change the layout you will need to modify the macro accordingly.
If you have blank spaces (no one assigned) in columns B to H, rows 6 to 45, the macro just skips those cells.
Name spelling is very important!! If the names are not spelled exactly the same in both ranges, you will get a miss count. You could use data validation to choose names in each cell from a drop down, but that becomes difficult with so many names (80).
Copying and pasting names might work better. You decide.


Here is what the Weekly Assignments portion looks like:

I'll post a picture...Excel Jenie and HTML are not working properly.


And here is the section showing the Number of shifts Assigned each employee:

I'll post a picture...Excel Jenie and HTML are not working properly.


With the worksheet open, copy the code below, then press 'Ctrl + F11' and paste the code into the window that opens. Then close that window and save your workbook as macro enabled.
After you have the weekly assignment portion filled out, and the employee names located as shown in columns J to S, save the file again.
You can run the macro using 'Alt+F8', select 'CntDups', then 'Run'. The number of shifts assigned for each employee will be shown beneath their name.
Perpa

Sub CntDups()
Dim cnt, col, col1, rw, rw1 As Long


    For rw = 6 To 27 Step 3
        For col = 10 To 19
            cnt = 0
            For rw1 = 6 To 45
                 For col1 = 2 To 8
                    If Cells(rw1, col1) = "" Then GoTo Skippit
                    If Cells(rw1, col1) = Cells(rw, col) Then
                        cnt = cnt + 1
                    End If
Skippit:
                Next col1


            Next rw1
    
            Cells(rw, col).Offset(1, 0) = cnt
    
        Next col
    Next rw
End Sub

 

[Perpa]

Here is what the Weekly Assignments portion looks like:

And here is the section showing the Number of shifts Assigned each employee: