Setting Alerts on multiple fields with dates to remind me to re-run the check in either 1 year, 3 ye

I have an HR onboarding master checklist that shows the dates certain forms were signed and the dates certain background checks were conduced. I need to set alerts to let me know when a check (which will have a date in the field) needs to be re-run. In some cases, the time frame is annually, but in other cases its every 3 or 5 years. How do I set up alerts like these on a lot of fields?

Maja

Find more posts tagged with

Human Resources

Services

Email Notifications

Comments

Schiff A.

Hello,

The Reminders function can send emails up to 14 days before or after a date. However, using additional date columns you can generate the reminder date needed and then set up a reminder based on the reminder date.

Here's how to do it:

1. Create a date column called "Reminder Date"

2. Enter the formula =DATE(YEAR([Original Date]@row) + #, MONTH([Original Date]@row), DAY([Original Date]@row))

Replace "#" with the number of years beyond the original date you need a reminder for.

This formula looks at the original date and reconstructs it with an updated year value.

3. Set up a reminder to trigger on the new date you generated.

Thanks!

Schiff

Smartsheet Support

Maja Roberts

Is there a simpler way to do this? I've got 12 columns and 100+ rows of dates. If I understand your response correctly, I'll need to set up a new date field with this formula for every field (that's at least 1,200 fields).

Maja

Andrée Starå

Hi Maja,

Can you upload a screenshot or share the sheet? It would make it easier to understand your use case and provide a solution.

Have you looked at setting it up as a project plan with dependencies?

Would that work for you?

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant / Get Done

Maja Roberts

Andree:

Thanks for responding. I'm attaching a screen shot. Haven't thought of doing it like a project plan with dependencies - I'm not sure how to do that.

Maja

Maja Roberts

Here is the screen shot.

Onboard ScreenShot.jpg

Andrée Starå

Hi again Maja,

I'd happily work with you and help you with setting up a solution for handling your HR process.

There are a few different ways to do this, and I need some more information to see what would work best for your particular situation.

One way to do it is as I wrote earlier, with a project plan. Depending on how many are involved in the process you could then have one sheet per person instead of everyone on one sheet. But it all depends on your specific use case.

If you'd like we can book a time for a free discovery call and take it from there.

Please e-mail me at andree@getdone.se and we'll take it from there.

How does that sound?

Have a fantastic week!

Best,

Andrée Starå - Workflow Consultant / Get Done

Maja Roberts

Schiff:

I tried the formula but cannot get it to work. Can you drill it down a bit for me? For example, I have a date in row 2, column 6 (name of the column is NDA). The date in that column is 12/29/2016. So, how would l write out the formula? I've tried various ways and not had any luck, so I'm hoping you could give me the secret with the one field and I can take it from there.

Many thanks!

Maja

Schiff A.

Hi Maja,

Here is how you would write the formula to set a reminder date that is 3 years after the NDA date:

=DATE(YEAR([NDA]@row) + 3, MONTH([NDA]@row), DAY([NDA]@row))

To break it down, here's what the formula is doing.

The formula creates a Date value by using the DATE function
The DATE function requires a year, month, and day.
The formula looks at the NDA column and first says take the year from the NDA date and add 3 (so in this example, it starts with 2016 and adds 3 to get 2019
Then the formula looks at the NDA column's month and day and adds those in as is.

You could also look at the formula working itself out for your example with where "[NDA]@row = 12/29/2016"

=DATE(YEAR([NDA]@row) + 3, MONTH([NDA]@row), DAY([NDA]@row))

=DATE(YEAR(12/29/2016) + 3, MONTH(12/29/2016), DAY([12/29/2016))

=DATE(2016+3, 12, 29)

=DATE(2019, 12, 29)

=12/29/2019

I hope that clarifies things a bit. Let us know if you have further questions.

Thanks,

Schiff

Maja Roberts

I'm still getting an "Unparseable" error. This is what I wrote: =DATE(YEAR([NDA/ Non-Sol Signed]@2) + 5, MONTH ([NDA/ Non-Sol Signed]@2), DAY ([NDA/ Non-Sol Signed]@2))

If you're saying I need to insert the date instead of the Column Name, then it doesn't seem to be saving any time for me to manually enter the date.

Maja Roberts

Finally figured it out...I knew it had to be simple. To add x years to a date in another cell, just type:

= (then click on the cell to add it here) + 1825 (if you want a date 5 years in the future it is 365 days x 5 years= 1825).

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.