Rotating Employee Schedule

[OnTarget22]

Hi all, first time poster (sorry if this is in the wrong place). I've dug myself into a task and don't even know if I'm over complicating an otherwise simple project. Any help would be greatly appreciated!

I've attached my very bare-bones mock-up of what I've trying to do. (I’m not married to it, so if there’s a simpler set up, let me know please!)

My goal is for all employees to do Task 1 and Task 2 once during one year. Employees can’t do both Task 1 and Task 2 in one month, and not in back-to-back months (ideally an employee would do Task 1 and then their name would rotate to the end of the list, which is when they’d do Task 2. Is that possible?). I’m hoping the sheet is flexible enough that it can accommodate changes in staffing (e.g., if someone leaves in March—they may have done Task 1, but they obviously won’t be here for Task 2. Also, if someone joins in June, they’re now in the rotation for both tasks eventually.)

On one Excel tab, I'd like to add or subtract employees as needed. Information includes job status (full time/part time/student intern: FT/PT/SI), first and last name, and tracking their completion of Tasks 1 & 2.

On another tab, I'd like for the names to populate into 2 tasks. If there's a way for Excel to filter: 1) if employee has completed said Task and 2) job status--I'm hoping for the program to select the first 2 FT, 1 PT, and 1 SI for each month and list their names for Task 1, as well as different names for 2 FT, 1 PT, and 1 SI for Task 2. And so on for each month…

Any thoughts are appreciated! (Even if I’m totally dreaming of the impossible)

[JeteMc]

Hello OnTarget22 and Welcome to Excel Forum.
I have attempted to automate assignments of tasks on the Staff List sheet although this proposal requires that some dates be manually placed (see rows where the cell in column A is highlighted in yellow).
The formulas that assign the remaining dates for Task 1 are similar to: =DATE(YEAR(D2),MONTH(D2)+1,1)
The formulas that assign the remaining dates for Task 2 are similar to: =IF(MONTH(E3)<12,DATE(YEAR(E2),MONTH(E2)+1,1),DATE(YEAR(E$2),MONTH(D4)-6,1))
Tasks that have been completed, are highlighted in green using: =MONTH(D2)< MONTH(TODAY())
The formula that populates Task 1 on the Task sheet is:

Please Login or Register  to view this content.

The formula for Task 2 is similar.
Let us know if you have any questions.

[OnTarget22]

I so appreciate your efforts! It was very kind of you to take the time to assist me.
You're helping me learn a few new tricks for using formulas in Excel.
Thanks so much!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.