Creating an Auto Scheduler for my Work’s Department

I realize I may go down a rabbit hole and this could potentially take me months or maybe even longer, but that’s fine I just need starting points for my research.

My experience:

Not a lot. Limited to extremely basic functions (e.g. SUM, IF, AND, OR), conditional formatting, data validation. Willing to delve into VBN but I know that requires basic knowledge of programming.

Goal:

There’s ~40 people in my department and we have an excel sheet that tracks all the daily tasks that need to be completed. There’s myself and a manager that makes the schedule for our respective sides of the week and it takes us 2-3 hours each week to assign each task one by one until the schedule is filled out. I would like to automate this in some kind of way.

Ideas:

- Assign a theoretical/average time to complete a task.

- Set each task as either an AM or PM shift specific task, and other tasks that can be completed by either shift.

- Assign people to a specific shift, and how many hours their shift is and on what days.

- If people have planned PTO, being able to update the available analysts that Excel will take into account.

- Excel does the work of auto-populating tasks with a person for each task accounting everything above and not assigning tasks that would cumulatively exceed the number of hours they’re at work, as well as averaging the workload between each person.

- Dynamic and editable after cells are auto-populated. Truthfully this is not required, but a quality of life. People call out, emergency situations might come up that might require attention over daily tasks, etc. My thought is I can copy the results and paste it into a separate sheet that can be editable as the week goes on.

Again I’m sure I have no idea how much work I’m asking for and it may be way above my capabilities, but as you roast me in the comments at least leave something that I can use to help me get started. Appreciate yall.