7 Day Shift Scheduling question

cmiles

New Member
Joined
Mar 19, 2018
Messages
1
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,
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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


Code:
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
 
Last edited:
Upvote 0
Here is what the Weekly Assignments portion looks like:
1Wbfn7b.jpg




And here is the section showing the Number of shifts Assigned each employee:
rIA1xXm.jpg
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top