Sending alerts in advance of start date

Natalie Gangeswaran

Hi all!

 

I need to schedule a row-by-row reminder 8 weeks in advance of the row start date. I've looked at the 'alerts' feature at the bottom of the sheet but it looks like the maximum I can send a reminder in advance of the start date is 14 days. Is there a work-around here?

 

The sheet I've created is a marketing calendar and each line is a new activity (with different start dates) and I'd like the reminder to be sent to the activity owner to tell them they need to brief the activity in. Hope that helps make sense of what I'm looking for!

 

Thanks in advance!

Brett Evans

That can be easily done.  just set a reminder date column, populate or calculate it and have your SmartSheet reminder point at that.

 

https://app.smartsheet.com/b/publish?EQBCT=19e517036f1f4efe9553c9e0c75b9824

Travis

To build on what Brett said, add a Reminder Date column, then you can either manually add a date or use a formula to show the date X days after your Start Date. This will update if your Start Date changes and you can drag fill this formula down your sheet. Then just set the reminder to that look to that column for the row or sheet. 

Natalie Gangeswaran

thanks both.

Is there a video I can watch that shows how to use a formula to automatically calculate the date 8 weeks before? I'm not sure how to do that, and it sounds easier than manually entering each one!!

Thanks

 

Travis

There isn't a video but I am happy to walk you through it!

 

I made a quick sheet to show you. Here it is:

 

https://app.smartsheet.com/b/publish?EQBCT=9ef2962d5e6a4a1f8b7baf57e293dc73

 

I added a Reminder Date column with a formula that grabs the date from the corresponding Start Date cell and subtracts 8 weeks. We dont have a week function for formulas so I did 8 * 7 (8 weeks x 7 days).

 

This is the formula:

 

=[Start Date]1 - (8 * 7)

 

This can be drag filled down the sheet by clicking in the cell containing the formula, and click and hold the bottom right corner of the cell and drag down your sheet.

 

You can then set a single sheet level reminder which can look to the Reminder Date column and will send a reminder on the date (only if there is a date on that row) to whoever is assigned to the task. This can be created from the Reminder tab at the bottom of your sheet. 

 

Let me know if you have any questions on this!

J. Craig Williams

Natalie,

 

One more useful bit is that Smartsheet has an "autofill" feature which will look at the rows above and below a new row to determine if there are formulas or formatting to be automatically added. Once you've got the Reminder formula setup on the column, when you won't need to add it again when you add new rows.

 

Craig

MichaelJ

1. Sorry for being obtuse but I don't see an option for "Reminder Date" column. See attached.

2. How does "Reminder Date" work? I think the user expectation is that a Reminder notification will be sent to the contact in the "Assigned To" column but I don't see any option for that under Notifications. 

 

Genevieve P.

Hello Michael,



1. A "Reminder Date" column is something that users manually set up by creating a "Date" column (from the list in your photo) and then titling it "Reminder Date." Then you can either manually add a date per row, or use a formula to show the date X days after your Start Date.

You can alternatively set up reminders right from a "Start Date" column, without needing an additional date column. Reminders can be set to go out on the date specified, or a certain number of days/weeks/months before hand. 

 

2. Once you have a column in your sheet with a date in it, and another column in your sheet that is a "Contact List" column with contacts in it, you can set up an Alert or Reminder based on that criteria. (Check out this Help Center article.)

In your Workflow, set up your TRIGGER to be as follows:



- Trigger: When Date is Reached

- Run Once: choose either on or a specific time before the date in your...

- Date Field (select the title of your Date column)

I've uploaded an example image of what this might look like if you want a reminder to go out 6 days before a date in a column titled "Start."

You can then Alert Someone, either by specifying exactly who to send it to, or by the Contacts in a Cell option where you choose the contact column previously set up. Then reminders would only be sent to the person in the same row as the date. 

Let me know if you have any questions about this!