How can I create 30-day reminders that repeat annually?

ajohnson303

I created a spreadsheet within Smartsheet to track our software licenses. Our licenses renew annually. I have created an "Expiration Date" field. What I'm trying to do is create a reminder that notifies me via email 30 days before the Expiration Date approaches. I would like for this task to be repeated annually. For example: If I have a license with an Expiration Date of 3/1/2025, I would like to receive an email on 2/1/2025 to remind me that a particular license will expire in 30 days. I would like for this recurrence to never end. Is this automation workflow task possible to do in Smartsheet?

Adam Murphy

Assuming you are updating the expiration date field each time, just add a helper column for "Reminder Date" and set it to "=[Expiration Date]@row - 30" (without quotes) and then set an automation to send a reminder (or even an update request that allows you to enter the new expiration date) using the when a date is reached trigger for that new helper cell.

Paul Newcome

Are you wanting this to run without you having to update dates every year?

ajohnson303

Exactly! I want it to repeat annually automatically

Paul Newcome

In that case, I would use a date type helper column with the following column formula.

=DATE(YEAR(TODAY()) + IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Initial Expiration Date]@row), DAY([Initial Expiration Date]@row)) - 30, 1, 0), MONTH([Initial Expiration Date]@row), DAY([Initial Expiration Date]@row)) - 30

The above is assuming you have a column that you are manually entering the initial expiration date into. If you would prefer to leverage a different date such as the License Purchase Date, we can do that too. You would just change your cell reference to that column.

ajohnson303

After speaking with my colleagues the Expiration Date might change each year. Using your formula for creating a 30-day reminder helper column I made a test spreadsheet with a 3-day reminder column. In this example, I created a workflow that will send me an email message 3 days before the expiration date is reached.

To test this out, I have software named AGI32 that expires on 7/27/2024. I set the Trigger to Run Once on the 3-Day Reminder date helper column. For the Conditions, I chose the following fields Software Name, Supplier, and Expiration Date. I set the Expiration Date is equal to 7/27/2024. Will I have to update the workflow to change the date annually each time the condition has been met? If so, is there a way for it to automatically update without me having to edit the workflow?

3DayWorkflowTest.PNG

Paul Newcome

You shouldn't need the date piece in the Condition.