Weekly rotating roles formulas

mtp105

Hello,

I am trying to create a report or calendar sheet with these weekly rotating roles:

1. Weekly 1st shift Call Center (Mon-Sun)
2. Weekly 2nd shift Call Center (Mon-Sun)
3. Weekend On-Call 1 (Sat-Sun)
4. Weekend On-Call 2 (Sat-Sun)

I have a sheet with the list of [Name]s and a [Resource #] column to use for creating the rotation formulas. I currently have 19 people.

-All 19 people will rotate through roles 1. and 2.

-Only select people will rotate through roles 3. and 4. I have a [Rotation Role] column to designate who can be used for the On-Call 1 or 2 roles.

So I'm trying to figure out formulas to calculate who will be in each of the 4 roles each day. The report would look like this: Where the date column would list each consecutive day, and the other columns would have the name of the person. Or if in a calendar view sheet, each day would list the names of the people in each role on that day.

Any help on how to create the rotation formulas would be greatly appreciated. Thanks!

jmyzk_cloudsmart_jp

Hi @mtp105

I fix the above to incorporated your second requirement and other errors, such as persons on 1st and 2nd shifts on weekends.

Smartsheet

Formulas

[Row] =MATCH(Date@row, Date:Date)

[On-Call 1] =IF(WEEKDAY(Date@row) = 7, 1)
[On-Call 2] =IF(WEEKDAY(Date@row) = 1, 1)
[WD1] =IF(OR([On-Call 1]@row, [On-Call 2]@row), "", [WD2]@row - 1)
[WD2] =IF(OR([On-Call 1]@row, [On-Call 2]@row), "", COUNTIFS(Row:Row, <=Row@row, [On-Call 1]:[On-Call 1], 0, [On-Call 2]:[On-Call 2], 0) * 2)
[Mod WD1] =IF(OR([On-Call 1]@row, [On-Call 2]@row), "", IF(MOD([WD1]@row, 19) = 0, 19, MOD([WD1]@row, 19)))
[Mod WD2] =IF(OR([On-Call 1]@row, [On-Call 2]@row), "", IF(MOD([WD2]@row, 19) = 0, 19, MOD([WD2]@row, 19)))
[WE1] =IF([On-Call 1]@row, COUNTIFS(Row:Row, <=Row@row, [On-Call 1]:[On-Call 1], true))
[WE2] =IF([On-Call 2]@row, COUNTIFS(Row:Row, <=Row@row, [On-Call 2]:[On-Call 2], true))
[Mod WE 1] =IF([On-Call 1]@row, IF(MOD([WE1]@row, [On-Call 1]#) = 0, [On-Call 1]#, MOD([WE1]@row, [On-Call 1]#)))
[Mod WE 2] =IF([On-Call 2]@row, IF(MOD([WE2]@row, [On-Call 2]#) = 0, [On-Call 2]#, MOD([WE2]@row, [On-Call 2]#)))
[S1] =IF(OR([On-Call 1]@row, [On-Call 2]@row), "", SMALL({resource_list : Resource #}, [Mod WD1]@row))
[S2] =IF(OR([On-Call 1]@row, [On-Call 2]@row), "", SMALL({resource_list : Resource #}, [Mod WD2]@row))
[S3] =IF([On-Call 1]@row, SMALL(COLLECT({resource_list : Resource #}, {resource_list : Rotation Role}, "On-Call 1"), [Mod WE 1]@row))
[S4] =IF([On-Call 2]@row, SMALL(COLLECT({resource_list : Resource #}, {resource_list : Rotation Role}, "On-Call 2"), [Mod WE 2]@row))
[Weekly 1st Shift Call Center] =JOIN(COLLECT({resource_list Name}, {resource_list : Resource #}, [S1]@row))
[Weekly 2nd Shift Call Center] =JOIN(COLLECT({resource_list Name}, {resource_list : Resource #}, [S2]@row))
[Weekend On-Call 1] =IF([On-Call 1]@row, JOIN(COLLECT({resource_list Name}, {resource_list : Resource #}, [S3]@row)))
[Weekend On-Call 2] =IF([On-Call 2]@row, JOIN(COLLECT({resource_list Name}, {resource_list : Resource #}, [S4]@row)))

Key Points:

1. Date Matching and Day Type Detection:
   • The [Row] formula identifies each row by matching dates sequentially.
   • [On-Call 1] and [On-Call 2] detect Saturdays and Sundays respectively.
2. Weekday Shift Management:
   • [WD1] and [WD2] calculate weekday shifts for 1st and 2nd shifts, excluding weekends.
   • [Mod WD1] and [Mod WD2] use modulo 19 to rotate the 19 resources evenly for weekday shifts.
3. Weekend Shift Management:
   • [WE1] and [WE2] count the occurrence of Saturdays and Sundays.
   • [Mod WE 1] and [Mod WE 2] use modulo based on the count of eligible resources for weekend roles to rotate them evenly.

Note:

Sheet Summary Fields, [On-Call 1]#, [On-Call 2]# count the number of person for those roles.

[On-Call 1] =COUNTIF({resource_list : Rotation Role}, "On-Call 1")

{resource_list } sheet image

jmyzk_cloudsmart_jp

I need to fix this comment to incorporate the following point;

-Only select people will rotate through roles 3. and 4. I have a [Rotation Role] column to designate who can be used for the On-Call 1 or 2 roles.

—- Not Complete —-

The following method creates an even rotation schedule for 19 people across weekday and weekend shifts by dynamically calculating resource assignments based on cumulative shifts and day type, ensuring balanced and accurate role distribution. 😊

Key Steps:

1. Determine Day Type:
   • Identifies if a date is a Saturday or Sunday using WEEKDAY function.
2. Assign Shift Count:
   • Weekdays get 2 shifts, weekends get 3 shifts for handling on-call roles.
3. Cumulative Shift Calculation:
   • Keeps a running total of shifts to manage rotation across all 19 resources.
4. Resource Indexing:
   • Uses modulo operation to rotate through the 19 resources smoothly.
5. Weekend Handling:
   • Separate logic ensures only eligible people fill on-call weekend roles.
6. Fetch Names Dynamically:
   • Uses COLLECT and JOIN to dynamically pull names based on calculated resource numbers.

Smartsheet

Formulas

[Year Day] =YEARDAY(Date@row)

[Weekend 1] =IF(WEEKDAY(Date@row) = 7, 1)
[Weekend 2] =IF(WEEKDAY(Date@row) = 1, 1)
[Shifts] =IF(OR([Weekend 1]@row, [Weekend 2]@row), 3, 2)
[Cumulative Shifts] =SUMIFS(Shifts:Shifts, [Year Day]:[Year Day], <=[Year Day]@row)
[S1] =[S2]@row - 1
[S2] =IF(OR([Weekend 1]@row, [Weekend 2]@row), MAX([S3]@row:[S4]@row) - 1, IF([Cumulative Shifts]@row = 19, 19, MOD([Cumulative Shifts]@row, 19)))
[S3] =IF([Weekend 1]@row, IF(MOD([Cumulative Shifts]@row, 19) = 0, 19, MOD([Cumulative Shifts]@row, 19)))
[S4] =IF([Weekend 2]@row, IF(MOD([Cumulative Shifts]@row, 19) = 0, 19, MOD([Cumulative Shifts]@row, 19)))
[Weekly 1st Shift Call Center] =JOIN(COLLECT({resource_list Name}, {resource_list : Resource #}, [S1]@row))
[Weekly 2nd Shift Call Center] =JOIN(COLLECT({resource_list Name}, {resource_list : Resource #}, [S2]@row))
[Weekend On-Call 1] =JOIN(COLLECT({resource_list Name}, {resource_list : Resource #}, [S3]@row))
[Weekend On-Call 2] =JOIN(COLLECT({resource_list Name}, {resource_list : Resource #}, [S4]@row))

The keypoint of this method is to get the shft number [S1] to [S4] for the four shifts, starting from [S3] or [S4] if the date is weekend, and if not starting from [S2], then, go backward subtracting one.

I used JOIN(COLLECT()), instead of INDEX(MATCH(), to avoid the use of the IFERROR function.

{resource_list : }

jmyzk_cloudsmart_jp

Hi @mtp105

I fix the above to incorporated your second requirement and other errors, such as persons on 1st and 2nd shifts on weekends.

Smartsheet

Formulas

[Row] =MATCH(Date@row, Date:Date)

[On-Call 1] =IF(WEEKDAY(Date@row) = 7, 1)
[On-Call 2] =IF(WEEKDAY(Date@row) = 1, 1)
[WD1] =IF(OR([On-Call 1]@row, [On-Call 2]@row), "", [WD2]@row - 1)
[WD2] =IF(OR([On-Call 1]@row, [On-Call 2]@row), "", COUNTIFS(Row:Row, <=Row@row, [On-Call 1]:[On-Call 1], 0, [On-Call 2]:[On-Call 2], 0) * 2)
[Mod WD1] =IF(OR([On-Call 1]@row, [On-Call 2]@row), "", IF(MOD([WD1]@row, 19) = 0, 19, MOD([WD1]@row, 19)))
[Mod WD2] =IF(OR([On-Call 1]@row, [On-Call 2]@row), "", IF(MOD([WD2]@row, 19) = 0, 19, MOD([WD2]@row, 19)))
[WE1] =IF([On-Call 1]@row, COUNTIFS(Row:Row, <=Row@row, [On-Call 1]:[On-Call 1], true))
[WE2] =IF([On-Call 2]@row, COUNTIFS(Row:Row, <=Row@row, [On-Call 2]:[On-Call 2], true))
[Mod WE 1] =IF([On-Call 1]@row, IF(MOD([WE1]@row, [On-Call 1]#) = 0, [On-Call 1]#, MOD([WE1]@row, [On-Call 1]#)))
[Mod WE 2] =IF([On-Call 2]@row, IF(MOD([WE2]@row, [On-Call 2]#) = 0, [On-Call 2]#, MOD([WE2]@row, [On-Call 2]#)))
[S1] =IF(OR([On-Call 1]@row, [On-Call 2]@row), "", SMALL({resource_list : Resource #}, [Mod WD1]@row))
[S2] =IF(OR([On-Call 1]@row, [On-Call 2]@row), "", SMALL({resource_list : Resource #}, [Mod WD2]@row))
[S3] =IF([On-Call 1]@row, SMALL(COLLECT({resource_list : Resource #}, {resource_list : Rotation Role}, "On-Call 1"), [Mod WE 1]@row))
[S4] =IF([On-Call 2]@row, SMALL(COLLECT({resource_list : Resource #}, {resource_list : Rotation Role}, "On-Call 2"), [Mod WE 2]@row))
[Weekly 1st Shift Call Center] =JOIN(COLLECT({resource_list Name}, {resource_list : Resource #}, [S1]@row))
[Weekly 2nd Shift Call Center] =JOIN(COLLECT({resource_list Name}, {resource_list : Resource #}, [S2]@row))
[Weekend On-Call 1] =IF([On-Call 1]@row, JOIN(COLLECT({resource_list Name}, {resource_list : Resource #}, [S3]@row)))
[Weekend On-Call 2] =IF([On-Call 2]@row, JOIN(COLLECT({resource_list Name}, {resource_list : Resource #}, [S4]@row)))

Key Points:

1. Date Matching and Day Type Detection:
   • The [Row] formula identifies each row by matching dates sequentially.
   • [On-Call 1] and [On-Call 2] detect Saturdays and Sundays respectively.
2. Weekday Shift Management:
   • [WD1] and [WD2] calculate weekday shifts for 1st and 2nd shifts, excluding weekends.
   • [Mod WD1] and [Mod WD2] use modulo 19 to rotate the 19 resources evenly for weekday shifts.
3. Weekend Shift Management:
   • [WE1] and [WE2] count the occurrence of Saturdays and Sundays.
   • [Mod WE 1] and [Mod WE 2] use modulo based on the count of eligible resources for weekend roles to rotate them evenly.

Note:

Sheet Summary Fields, [On-Call 1]#, [On-Call 2]# count the number of person for those roles.

[On-Call 1] =COUNTIF({resource_list : Rotation Role}, "On-Call 1")

{resource_list } sheet image

mtp105

@jmyzk_cloudsmart_jp Thank you so much for all this work! I really appreciate it. I'll give it a try. This really helps.

jmyzk_cloudsmart_jp

@mtp105

Happy to help!😁

If you need a copy of those sheets, please get in touch with me.

(My email is in the profile page.)