In this
article we look at a simple way of automatically creating work shifts schedules in Excel. Nothing we couldn’t do manually but can save some tedious work
instead. It’s a good example to understand how to work with different sheets,
use loops, simple calculations and format the data with VBA.
The main
console allows to input up to 20 resources and build the shift structure
within 20 lines. Each resource is supposed to be 1 FTE, working 8 hours per day
five days a week (40 hours per week). If that's not the case in your country, you can still use it and it will not impact the shift
and final schedule; the system will just warn you before continuing. In order
to create the shifts you need to input a shift number as many times as needed
(a simple Monday to Friday 8 hours shift will just require one single line,
but more complex combinations may need additional rows). Please be aware there
are 2 hidden columns to calculate week days.
Create Shifts
The first
macro walks through the shifts table and puts them together as per shift
number, then it copies each shift number in a friendly format along with the
week hours and FTE requirement per shift into the “Shifts” sheet.
Find the code in the updated file in this post from Excel Macro Business.
Updates to
the shifts name and any other adjustments to the shift table is possible at this
point directly on the sheet. Some simple calculations at the bottom warn if FTE
requirements are not met with the resources proposed in the “Console” tab.
Create Roster
Once ready,
select a date to align with month and year desired and create a monthly or full
year roster/schedule. The shift the first resource will
start working with can also be selected. The rotation type in this simple version is
set to “weekly”.
Find the code in the updated file in this post from Excel Macro Business.
I hope you find this useful and can be applied to your business/organization. Let me know if you have any questions or need some adjustments to make it work for you.
No comments:
Post a Comment