Kirk was very helpful in me with this excel workbook. I am seeking how to change the excel workbook days off from 5 day workweek to 4 day work week. Also, would like the ability to change to 12 hr days with 3 days on 2 days off and next week 2 days on and 3 days off. I appreciate the assistance.
www.dropbox.com
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Excel Help
- Thread starter FLdave12
- Start date
Jeffrey Mahoney
Well-known Member
- Joined
- May 31, 2015
- Messages
- 3,247
- Office Version
- 365
- Platform
- Windows
Your Dropbox link is going to exclude many people that can help including me. Showing examples of your current results along with your expected results using XL2BB will get more replies.
Upvote
0
The calendar-building is called "MonthCal_SBdo" (monthly calendar Sorted By days off). MonthCal_SBdo is called repeatedly by a single formula on each shift worksheet so that the monthly calendar outputs can be stacked together...so that all months within the total calendar range are shown on a single worksheet for each shift. You should see a reference to this function in the formula appearing in cell A3 on each shift worksheet...or wherever the upper left corner of the calendar stack is found. To see details about the function, the best way is to open up the Advanced Formula Environment (Home > Excel Labs > AFE) and then choose the Names tab and select the Edit icon associated with MonthCal_SBdo. This opens an editor where the entire complex function can be seen and edited. The changes mentioned have been incorporated into this function, so you won't have to make those edits...but this is how you'd access the function should it become necessary.
As for future expansion, the current formula dynamically adjusts the calendar size to account for everyone scheduled. There are no extra/empty slots for classification specialists, but if you want extra specialists, just schedule them and the monthly calendar should expand to accommodate them.
This version includes artefacts from the 3-shift scheduling model, as well as a 2-shift scheduling model. The point being that some parts can be removed once you've finalized the scheduling system. I've made the following changes:
In the List worksheet, for Days Off, the two-day codes are now three-day codes. For example, someone who originally had a Sunday-Monday (SM) "weekend" now would have a longer Sunday-Monday-Tuesday (SMT) "weekend". Notations on the Master worksheet for staff "weekend" days off mirror these three-day codes.
The MonthCal_SBdo (monthly calendar generating function) includes the features to sort by days off and to treat "weekends" as three-day weekends.
Rather than having a dedicated worksheet for Split Shift Supervisor, you might find something like the "Supervisory Summary2" or "Supervisory Summary3" worksheet useful. These worksheets are very similar, one being intended for a 3-shift solution, the other intended for a 2-shift solution. These sheets aggregate the "total" lines from each shift and each month from the outputs on the various shift worksheets and then identify where 0 coverage is currently planned...and those are automatically color-coded using Conditional Formatting. Furthermore, a tabulation of those dates is also made, which should make it convenient to identify and schedule extra supervisory help where needed.
Give this version I try to see if it does what you want.
www.dropbox.com
As for future expansion, the current formula dynamically adjusts the calendar size to account for everyone scheduled. There are no extra/empty slots for classification specialists, but if you want extra specialists, just schedule them and the monthly calendar should expand to accommodate them.
This version includes artefacts from the 3-shift scheduling model, as well as a 2-shift scheduling model. The point being that some parts can be removed once you've finalized the scheduling system. I've made the following changes:
In the List worksheet, for Days Off, the two-day codes are now three-day codes. For example, someone who originally had a Sunday-Monday (SM) "weekend" now would have a longer Sunday-Monday-Tuesday (SMT) "weekend". Notations on the Master worksheet for staff "weekend" days off mirror these three-day codes.
The MonthCal_SBdo (monthly calendar generating function) includes the features to sort by days off and to treat "weekends" as three-day weekends.
Rather than having a dedicated worksheet for Split Shift Supervisor, you might find something like the "Supervisory Summary2" or "Supervisory Summary3" worksheet useful. These worksheets are very similar, one being intended for a 3-shift solution, the other intended for a 2-shift solution. These sheets aggregate the "total" lines from each shift and each month from the outputs on the various shift worksheets and then identify where 0 coverage is currently planned...and those are automatically color-coded using Conditional Formatting. Furthermore, a tabulation of those dates is also made, which should make it convenient to identify and schedule extra supervisory help where needed.
Give this version I try to see if it does what you want.
Dropbox
Upvote
0
Jeffrey Mahoney
Well-known Member
- Joined
- May 31, 2015
- Messages
- 3,247
- Office Version
- 365
- Platform
- Windows
Click on the link, download it, save it, add it as an add-on in Excel. Highlight a range and select Mini Sheet from the XL2BB menu
Upvote
0
Everything is working great. I appreciate the help. I do have a question. If work schedule is to become 12 hr shifts with rotating days off is that possible in excel?
There would be 4 Shifts: A Squad Days & B Squad Days then A Squad Nights and B Squad Nights. Below is how days off would go from week 1 to week 2. Appreciate your thoughts
Week 1 A Shift Days & Nights Work Schedule we be: Sunday Work, Monday Off, Tuesday Off, Wednesday Work, Thursday Work, Friday Off, Saturday Off
Week 2 A Shift Days & Nights Work Schedule would be: Sunday Off, Monday Work, Tuesday Work, Wednesday Off, Thursday Off, Friday Work, Saturday Work
Week 1 B Shift Days & Nights Work Schedule would be: Sunday Off, Monday Work, Tuesday Work, Wednesday Off, Thursday Off, Friday Work, Saturday Work
Week 2 B Shift Days & Nights Work Schedule would be: Sunday Work, Monday Off, Tuesday Off, Wednesday Work, Thursday Work, Friday Off, Saturday Off
There would be 4 Shifts: A Squad Days & B Squad Days then A Squad Nights and B Squad Nights. Below is how days off would go from week 1 to week 2. Appreciate your thoughts
Week 1 A Shift Days & Nights Work Schedule we be: Sunday Work, Monday Off, Tuesday Off, Wednesday Work, Thursday Work, Friday Off, Saturday Off
Week 2 A Shift Days & Nights Work Schedule would be: Sunday Off, Monday Work, Tuesday Work, Wednesday Off, Thursday Off, Friday Work, Saturday Work
Week 1 B Shift Days & Nights Work Schedule would be: Sunday Off, Monday Work, Tuesday Work, Wednesday Off, Thursday Off, Friday Work, Saturday Work
Week 2 B Shift Days & Nights Work Schedule would be: Sunday Work, Monday Off, Tuesday Off, Wednesday Work, Thursday Work, Friday Off, Saturday Off
Upvote
0
