Is there any way have date based reminders go out weekly?

Ariana Arden

I'm trying to send out a date based reminder, but I only want it to go out once a week. When we come within a week of date in Column A, I want to be alerted, but weekly instead of daily. Is there any way to do this?

Matthew Flebbe

Hi Ariana,

There is a way to do this. First you will need two DATE columns:

Due Date

Reminder

We will use a logical formula in the REMINDER DATE column so Smartsheet knows which day of the week it is, and an automated alert that incorporates the REMINDER DATE. In the example formula below, I have used Wednesday as the day of the week for the reminder alert.

The formula we use for this is a nested formula that incorporates the IF function along with the WEEKDAY function and some simple subtraction. The WEEKDAY formula will tell you on which day of the week a referenced date falls. In a short example, today is Thursday so =WEEKDAY(TODAY()) will result in the number 5 because Thursday is the fifth day of the week.

I have constructed the formula below to alert when the DUE DATE is 7 or less days away and I have set the reminder date to always be on a Wednesday. If you prefer to send alerts on a different day, the formula may be altered accordingly. 

Here is the formula for Wednesday reminders that you will also want to convert to a Column Formula:

=IF(WEEKDAY([Due Date]@row) = 4, [Due Date]@row - 7, IF(WEEKDAY([Due Date]@row) = 5, [Due Date]@row - 1, IF(WEEKDAY([Due Date]@row) = 6, [Due Date]@row - 2, IF(WEEKDAY([Due Date]@row) = 7, [Due Date]@row - 3, IF(WEEKDAY([Due Date]@row) = 1, [Due Date]@row - 4, IF(WEEKDAY([Due Date]@row) = 2, [Due Date]@row - 5, IF(WEEKDAY([Due Date]@row) = 3, [Due Date]@row - 6, [Due Date]@row)))))))

Now we will use the Alert Automation to send an alert to a user "When a date is reached." Note that alerts will only be sent once so if your REMINDER DATE falls on a Wednesday, you may want to set up a second alert for items due on the DUE DATE with some conditional logic around whether the task has already been completed.

The time at which the alert is sent will be determined using the Automation (image below).  

I hope that helps. If you have any follow up questions, please feel free to reach out to me.

best,

Matt

Andrée Starå

Hi @Ariana Arden

I hope you're well and safe!

Here's one way to do it.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!