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

Maja Roberts

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

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.

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).