How to send reminder/notification 14 days ahead of Due Dates?

1rider

I have searched the internet for this answer and have come up short so far in finding an answer.

I am trying to set up notifications to be triggered by creating an Automation rule, formula or combination of both that emails the contact person on the row in the Contact column I created. The desired result is that the person receives notification 14 days prior to the Calibration Due Date as seen in the screenshot. The notification will need to be sent again 7 days in advance of the Calibration Due Date.

Calibration SS.docx

Dan W

Hope this helps. Here is also a SS YouTube video on the subject

https://www.youtube.com/watch?v=ND40ZD5WYdE

1rider

Thanks Dan. I have tried that and it doesn't seem to work. It may be because I have Conditional Formatting for the Calibration Due Date already in place?

Side note- It is annoying that the notification send time is only available at the top of each hour, meaning I have to wait to see if my changes are working properly.

Ella

@1rider try using "When a date is reached" alert. Then you select the alert to run 14 days prior Calibration Due Date. Copy the alert and change it to run 7 days prior your due date.

1rider

https://community.smartsheet.com/discussion/comment/323008#Comment_323008

Hi Ella, thank you. The closest thing in the dropdown to what you have called " prior to" is "in the next (days)" for which I have selected 14 and 7 days in a separate automation rule.

Dan W

@1rider

When testing automations I usually set the trigger to when rows are added or changed

and

When: any field changes.

This allows me to make a change and it will trigger right away.

Some date functions are tricky. Have you thought about adding a "Helper" Column?

You could make a date column that is the Due date - 14 and -7 respectfully. Then make an automation that alerts when the helper column date is reached. You could also hide those columns so they don't bog down your sheet.

Dan W

Example

1rider

https://community.smartsheet.com/discussion/comment/323019#Comment_323019

Dan, that last bit is where I am at now and you described exactly what I wanted to try and do inlcuding hiding the column. I am wrestling with the correct way to write the formula now so that it works.

Also, great suggestion on using a change as a trigger for testing notifications. Thank you!