How to generate shifts in excel

[elcafe31]

Hello Team,

Is there a way to effectively generate a set of shifts or schedule with the given required headcount per interval in a 24/7 coverage?
Let's say;

* Required headcount per 30-min interval was given (Monday to Sunday)
* 2 days off per employee
- 4 Hours for Part-Time
- 9 Hours for Full Time
* Part-Time headcount is 100
* Full Time headcount is 50


I'm using countif formula right now guessing the shifts and countif will calculate the numbers per interval, then I compare it to the required headcount per interval from other file.

The real problem is to generate shifts from the required headcount file instead of guessing and comparing the numbers.

ChatGPT is not helping, or maybe I am just too dumb

 

[dnorm]

Hi elcafe31

I think there is a few variables missing from your request.

Are the employees on static shifts, and what is the manning requirement for each day/shift/hour (as you have requested 30min intervals)?
Is the manning requirement the same for everyday of the week?

​

 

[elcafe31]

Right now, shift for each employee is not static. But will be adding new employees with flexible schedule soon to meet the demands. The staffing requirement differs every 30 mins, every day, and every week (please see image attached).

I am having a hard time generating schedules that would cover at least 85% of the staffing requirement.

 

[elcafe31]

Hi elcafe31

I think there is a few variables missing from your request.

Are the employees on static shifts, and what is the manning requirement for each day/shift/hour (as you have requested 30min intervals)?
Is the manning requirement the same for everyday of the week?

​

Right now, shift for each employee is not static. But will be adding new employees with flexible schedule soon to meet the demands. The staffing requirement differs every 30 mins, every day, and every week (please see image attached).

I am having a hard time generating schedules that would cover at least 85% of the staffing requirement.