Rotating work schedule

Tiffany McKerahan

Hi Smartsheet Community! I need to set up a rotating work schedule in which there are 7 work days—A through G—that rotate throughout a Monday-Friday week. So, for example:

• Monday: day A
• Tuesday: day B
• Wednesday: day C
• Thursday: day D
• Friday: day E
• Monday: day F
• Tuesday: day G
• Wednesday: day A

I want to assign my team specific tasks based on the day's letter, while skipping weekends and holidays. I'm stuck on how to begin; what does the community recommend?

Thanks for your help!

Tiffany

Paul Newcome

I believe I may have missed a closing parenthesis near the end. Try this one:

=IFERROR(INDEX({Day Pairings Day Letter}, IF(MOD(IF(AND(Helper@row <> "Holiday", Helper@row <> "Weekend"), COUNTIFS(Helper:Helper, AND(@cell <> "Weekend", @cell <> "Holiday"), Number:Number, @cell <= Number@row)), 7) = 0, 7, MOD(IF(AND(Helper@row <> "Holiday", Helper@row <> "Weekend"), COUNTIFS(Helper:Helper, AND(@cell <> "Weekend", @cell <> "Holiday"), Number:Number, @cell <= Number@row)), 7))), "")

Paul Newcome

Happy to help. 👍️

And if you wanted to make it even more flexible so that you could use the same thing for more or less people and just manage it directly from your reference list, you would replace each "7" with a COUNT function that simply counts how many letters are in the letter column of your reference list.

COUNT({Day Pairings Day Letter})

Paul Newcome

Are you able to provide a screenshot of a mock-up for this one?

Tiffany McKerahan

Hi Paul,

Thanks for responding. Below is what I've mocked up manually but I'd like to figure out how to do it automatically.

Paul Newcome

Which part(s) exactly are you wanting to automate? There is going to have to be some sort of manual entry at some point to give it somewhere to start, but we should be able to automate the majority of it depending.

Tiffany McKerahan

I'd like to automate all of it, ideally, but I realize that I'd have to start with some manual entry. I'd like the weekends and holidays to be detected automatically so that the letters in the first column skip those days as shown in the manual screenshot. I'd also like the order of the names in the middle column to be applied automatically, so for example, Janice always follows Adam (but skipping those weekends or holidays as needed).

Paul Newcome

So would you be entering the dates manually or some sort of start date at least?

Tiffany McKerahan

I could enter a start date manually but I'd prefer not to enter ALL the dates manually.

Paul Newcome

Ok. So far so good for a solution. Next question… Do you have a list of holidays or can you create a separate sheet with a list of holidays?

Tiffany McKerahan

Sure, I can create a separate sheet of holidays.

Paul Newcome

Excellent. Ok. So the first thing we will need is a text/number column (called "Number" in this example). This column will be populated with the numbers 0 through however many days you will need to accommodate going down the rows. So if you want your schedule to go for a year, you are going to want to do the numbers 0 - 365.

0

1

2

3

etc.

Then I would use a Sheet Summary field for the start date.

The Date column would use this column formula:

=[Start Date]# + Number@row

Next, you would use a text/number column (called "Helper" in this example) with this:

=IF(OR(WEEKDAY(Date@row) = 7, WEEKDAY(Date@row) = 1), "Weekend", IF(COUNTIFS({Reference Sheet Holiday Date Column}, @cell = Date@row) > 0, "Holiday", "Work Day"))

Finally I would create two more columns in my reference sheet. One for the day's letter and one for the person assigned to each letter.

A…..John

B…..Sue

C…..Tiffany

Then you can use an INDEX function to pull in the letter and person. The below is for the letter. You would only need to change the very first range inside of the INDEX to pull the person.

=IFERROR(INDEX({Reference Sheet Letter Column}, IF(MOD(IF(AND(Helper@row <> "Holiday", Helper@row <> "Weekend"), COUNTIFS(Helper:Helper, AND(@cell <> "Weekend", @cell <> "Holiday"), Number:Number, @cell <= Number@row)), 7) = 0, 7, MOD(IF(AND(Helper@row <> "Holiday", Helper@row <> "Weekend"), COUNTIFS(Helper:Helper, AND(@cell <> "Weekend", @cell <> "Holiday"), Number:Number, @cell <= Number@row)), 7)), "")

We can make the above even more dynamic if needed so that you can add more letters / people to your reference list and automatically have everything adjust accordingly. As it is, the formula is set for specifically 7.

Tiffany McKerahan

Paul, you're a gem! Thank you for your work on this. My only issue is that I get "#INCORRECT ARGUMENT SET" when I paste in the INDEX function you outlined. Here's what I have:

=IFERROR(INDEX({Day Pairings Day Letter}, IF(MOD(IF(AND(Helper@row <> "Holiday", Helper@row <> "Weekend"), COUNTIFS(Helper:Helper, AND(@cell <> "Weekend", @cell <> "Holiday"), Number:Number, @cell <= Number@row)), 7) = 0, 7, MOD(IF(AND(Helper@row <> "Holiday", Helper@row <> "Weekend"), COUNTIFS(Helper:Helper, AND(@cell <> "Weekend", @cell <> "Holiday"), Number:Number, @cell <= Number@row)), 7)), ""))

I'm hoping this is something easy to diagnose?

Paul Newcome

I believe I may have missed a closing parenthesis near the end. Try this one:

=IFERROR(INDEX({Day Pairings Day Letter}, IF(MOD(IF(AND(Helper@row <> "Holiday", Helper@row <> "Weekend"), COUNTIFS(Helper:Helper, AND(@cell <> "Weekend", @cell <> "Holiday"), Number:Number, @cell <= Number@row)), 7) = 0, 7, MOD(IF(AND(Helper@row <> "Holiday", Helper@row <> "Weekend"), COUNTIFS(Helper:Helper, AND(@cell <> "Weekend", @cell <> "Holiday"), Number:Number, @cell <= Number@row)), 7))), "")

Tiffany McKerahan

Yes, that worked perfectly! I'm all set, thanks SO much!

Paul Newcome

Happy to help. 👍️

And if you wanted to make it even more flexible so that you could use the same thing for more or less people and just manage it directly from your reference list, you would replace each "7" with a COUNT function that simply counts how many letters are in the letter column of your reference list.

COUNT({Day Pairings Day Letter})