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
Comments
-
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
-
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
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
Here is the screen shot.
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
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
-
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.
-
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).
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives