Monthly rolling schedules for multiple employees

Hello!

Please see attached work schedule for Users 1 through 11. I’m looking for the right formulas that will help me achieve all the items mentioned below, perhaps with a visual tool or table that can show me where I have gaps in the schedule or overlapping schedules and I'd like to also include total working hours for each employee (User). As you can see, this is a time consuming task as I am doing this manually (without the use of formulas):

WORK SCHEDULE

• There are 11 employees total, named User 1 through User 11 on the sheet.
• Some employees work 20 hours, others 24, others 40 or 48 per week.
• Day shift is between 8am and 11pm and nightshift is between 11pm and 8am.
• User 1 through 10 work the day shift only.
• User 11 works the night shift only, with Saturday nights off. On said night off, User 5 covers the nightshift hours. Therefore, User 5 and User 11 are the only ones ever on Night Shift.
• Each dayshift (between 8am and 11pm) there should be at least 3 employees on work schedule at all times. For example, I do not want gaps where, let's say between 3pm and 4pm there was only one person on Work schedule.

TASK A AND TASK B SCHEDULE

• There are two additional parallel schedules on the sheet. Example: User "n" is scheduled on Work Schedule between 8am-4pm and, during said hours, is assigned to Task A from 9am to 11am and is also assigned to Task B from 12pm to 4pm. In other words, the assigned schedules for Task A and Task B are contained within the work schedule hours.
• Task A and Task B scheduled hours should not overlap - example: User 1 and User 2 cannot be both assigned to Task A between 1pm and 2pm, only one employee at a time for each task. Task A and Task B are also mutually exclusive. Therefore, If User 1 is assigned to Task A between 1pm and 3pm, he cannot also be assigned to Task B during those hours. Task B must be assigned to another User during said hours. The only times Task A and Task B are allowed to overlap is during the night shift (between 11pm and 8am), since there is only one person on night shift who therefore has to cover both tasks during his entire shift.
• The schedule is 24/7.

Any help would be much appreciated as this has become very time consuming, mainly due to the managing of multiple schedules, with schedules within the schedules. Thank you in advance and I look forward to learning from you.

Kind regards

Sorry for the delay.

To run just press the Assign Tasks button located in the Tasks Tab
That´s the easy part.

To build the macro, there were some changes to the original schedule. I translated everything to a 24 hour format.
I went to the original schedule to fill it out the cells correctly with am and pm. Check on that.

Other than that, is this similar of what you wanted? You did not provide an output sample..

Regards,

Rodrigo

Hi. Thank you for the response. Great advice and much appreciated. I apologize for not being more specific as I was a bit lost at first. I have worked on the file myself and now have a version of what I was looking for. Please see attached. I created a Gantt Chart for each day of the month (hidden by the Group tabs up top). To view the coverage for each day you can open the groupd tabs. I did the same for all three schedules: Work schedule, Messages (Task A) and PE (Task B). This gives the visual tool I was looking for to easily see if there is proper coverage. For work schedule, which is the most important of all three, I also added a column counting hours worked per day.

Within the Gantt chart I am having an issue with my IF formula. Please see cell T11. Anthony is scheduled to work from 6pm to 10m, however, the Gantt chart also highlights the 10th hours, as if it is a working hour for Anthony but it is not. This seems to be working fine with the other cells within that same day (3rd of the month). Any ideas on what may be wrong with my formula?

Thanks in advance!!

Andres