Prevent due dates from falling within the same calendar week?

Zach_

Hi.

I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send them more than one document per week.

Here are the columns I'm using now:

• Last Reviewed Date is starting off as manually entered (by me), but once this process is in motion, this field will be updated by automation as reviews are completed.
• Next Review Due adds 365 days to the Last Reviewed Date. If that date is not a Friday, it displays the date of the very next Friday. My partners have requested that their reviews always be due on Friday.
• Solicit Review Trigger Date references the Friday displayed in Next Review Due, then displays the Monday immediately preceding it. My partners have requested that their review requests always be sent on Monday.

My questions: What formula(s) and helper column(s) do I need to ensure that every row has a unique combination of calendar year + week number? How can I avoid sending review requests during particular weeks of the year (e.g., Thanksgiving and the week between Christmas and New Year's)?

Cayla Davis

Try this out - hope it helps!

Part 1: Creating a Unique Year + Week Combination 

1. Add a Helper Column: Create a new column (e.g., "Year-Week") in your Smartsheet.
2. Enter the Formula: In the first cell of this new column, enter the following formula, replacing [Your Date Column] with the name of the column containing the relevant date for that row:

Code

    =YEAR([Your Date Column]@row ) + "-" + WEEKNUMBER([Your Date Column]@row )

This formula will combine the year and week number into a single text string (e.g., "2023-45"), ensuring a unique identifier for each combination. 

1. Apply the Formula: Drag the fill handle (the small circle in the bottom-right corner of the cell) down to apply the formula to all rows in your sheet.

Part 2: Avoiding Review Requests During Specific Weeks 

1. Add a Flagging Column: Create a new column, such as "Skip Review," which can be a symbol or checkbox column.
2. Use the Formula for Holiday Weeks: In the "Skip Review" column, use a formula to flag specific weeks. For example, to skip Thanksgiving week (assuming it's week 46, which can vary) and the week between Christmas and New Year's (week 52):

Code

    =IF(OR(WEEKNUMBER([Your Date Date]@row ) = 46, WEEKNUMBER([Your Date Date]@row ) = 52), true, false)

• Note: The WEEKNUMBER function's definition of week numbers can vary, so you may need to verify the exact numbers for the weeks you want to exclude. 

1. Reference the Flag in Automation: In your review request automation, add a condition to check the "Skip Review" column. If the column contains a true value (or a checkmark), you can set the automation to not send the request for that row.