Anyone have ideas on creating work roster with auto-fill (automation) duties?
Objective:
creating 4 week roster
3 shifts (day, evening, night)
Rules:
rotating day and evening shift
night duty is 3 night straight and ends with a day off
per staff 2 day off per week
30 staffs.
morning staffs and afternoon staffs should be balance
night always have 4 staffs
To create:
Each staff able to put day or evening at random for any 5 days of the whole roster (28 days).
analyse the duty staff requests and randomly fill up to suit the above rules.
Is it possible in excel with macro / VBA?
My ideas:
create a sum function"balancing staffs" row for 3 shifts (AM, PM, ND).
create a sum function for each staff check to ensure staff taking 2 days off per week. (8 off days)
coding to protect staff request to avoid changing
Search / Auto find Night request and coding for auto fill night shift duty (night, night, night, day off) to empty cells.
coding to Auto fill night column (4 days) for other staffs and coding to ensure night shift balance have 4 staffs.
coding to Random fill morning and evening duties per staffs for all empty cells and
coding to check balance morning and evening duties for all empty cells.
coding to check day off per week and randomly replace day off
Coding to Replace morning and evening to day off per week randomly
check balance again for morning and evening duties. Loop.
coding to check day off per week.
Bookmarks