Rotating work schedule
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
Best Answers
-
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))), "")
-
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})
Answers
-
Are you able to provide a screenshot of a mock-up for this one?
-
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.
-
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.
-
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).
-
So would you be entering the dates manually or some sort of start date at least?
-
I could enter a start date manually but I'd prefer not to enter ALL the dates manually.
-
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?
-
Sure, I can create a separate sheet of holidays.
-
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.
-
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?
-
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))), "")
-
Yes, that worked perfectly! I'm all set, thanks SO much!
-
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})
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!