Annual Date Alert/Reminder

Marijan

Hi. I have an employee data sheet with birth dates in one column. How can I set an annual alert that will remind me every year on the day and month their birthday falls?

As you can see above, currently at the end/beginning of every year, I search and replace the year in the Birthday column to the current/next year (now "2020") and the Alert is set to run every day and remind me at 7:00 am that it is an Employee's birthday.

Thank you

Andrée Starå

@Marijan

Glad we got it working!

For anyone else.

I'd recommend creating a so-called helper column. We could call it, Reminder. Then you could add the formula below.

=DATE(YEAR(TODAY()); MONTH(Birthday@row); DAY(Birthday@row))

The same version but with the below changes for convenience.

=DATE(YEAR(TODAY()), MONTH(Birthday@row), DAY(Birthday@row))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi Marijan,

You can setup a yearly recurring alert with the Custom Recurrence feature.

https://help.smartsheet.com/learning-track/smartsheet-intermediate/alerts-reminders

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Marijan

The above will only work if I continue to update the year in the Birthday field to the current years. Is there a way to do this so I get the reminder regardless of they are years were born?

Andrée Starå

No, you won't need to update the year.

Smartsheet will send the reminder once a year until you stop it or until an end date that you select.

Make sense?

Marijan

I have tested this by setting the date to today, 1982 and tried to run it at 10 am and 11 am and have not received any alerts. It does work when I change the years back to 2020.

Andrée Starå

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Marijan

Hi Andree - I have shared a small portion of this sheet. Thank you

Andrée Starå

@Marijan

Glad we got it working!

For anyone else.

I'd recommend creating a so-called helper column. We could call it, Reminder. Then you could add the formula below.

=DATE(YEAR(TODAY()); MONTH(Birthday@row); DAY(Birthday@row))

The same version but with the below changes for convenience.

=DATE(YEAR(TODAY()), MONTH(Birthday@row), DAY(Birthday@row))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Emmet McKenna

Hi @Andrée Starå I want to do something similar to the above, i.e. set reminders based on the Date/Month and send the reminder annually. I have setup a Reminder column but the formula you suggested is showing up as #UNPARSEABLE.

The column with the date is 'Date of Birth' and I've entered the below formula into the 'Reminder' column. What does the first section of the formula "=DATE(YEAR(TODAY()), " do?

=DATE(YEAR(TODAY()), MONTH(Date of Birth@row), DAY(Date of Birth@row))

Andrée Starå

Hi @Emmet McKenna

Try something like this instead.

=DATE(YEAR(TODAY()), MONTH([Date of Birth]@row), DAY([Date of Birth]@row))

Did that work?

I hope that helps!

Have a fantastic weekend & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Emmet McKenna

That works perfectly @Andrée Starå. Thank you!

Andrée Starå

@Emmet McKenna

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Sonja Liebenberg

Hi Andree,

I am trying the same thing. Setting up a recurring birthday reminder without having to change the year, on an annual basis.

I tried the Helper Column formulas suggested above.

The 1st one returns Unparsebale and the 2nd one returns: - Invalid Column Value.

What am I doing wrong?

Regards

Sonja

Andrée Starå

Hi @Sonja Liebenberg

I hope you're well and safe!

I'd be happy to take a quick look.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Sonja Liebenberg

Hi Andree

I sent you an email with a screen print

Sonja Liebenberg

Hi Andree, can you please have a look at this today? I shared a copy of my sheet with you yesterday afternoon.

Your help will be appreciated.