Birthdate Reminder or alert

Sue van Niekerk

HI Everyone

I am battling to set up a birthday reminder

I have my DOB date column and in a another column am trying to convert the birthdate to a current year date so I can use it for Birthday reminders but I am really battling

Have tried coping a number of examples but they just dont work

Andrée Starå

@Sue van Niekerk

Glad we got it working!

✅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 @Sue van Niekerk

I hope you're well and safe!

Try something like this.

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

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

Did that work/help?

I hope that helps!

Be safe and have a fantastic day!

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!

Sue van Niekerk

HI

It says #UNPARSEABLE

Andrée Starå

@Sue van Niekerk

You might need to update the column names.

If you want, 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, [email protected])

Sue van Niekerk

Shared :-)

Andrée Starå

@Sue van Niekerk

Glad we got it working!

✅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!

Raquel Love

@Andrée Starå - I have tried this formula and it only works if you continually update the Year in the DOB column. Is there a formula to use in a Reminder column that allows a Birthday Alert automation to run every year without having to manually update the DOB column?

Andrée Starå

Hi @Raquel Love

I hope you're well and safe!

This one should work.

What happens?

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

Did 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(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!

Raquel Love

Hi @Andrée Starå. Thanks for the quick response! What happened when I used this formula in my Reminder column is that when the calendar turned over to 2022 my automations stopped working - currently set to send an alert when the current date is reached in the Reminder column.

What is the expected behaviour of the =DATE(YEAR(TODAY()); MONTH(DOB@row); DAY(DOB@row)) formula? Example below:

DOB Date - January 1, 1988

Reminder Date - January 1, 2022 (because of the formula)

Calendar changes to 2023 - does formula in the Reminder Date column cause the date in the field to change from January 1, 2022 to January 1, 2023?

My automation is set as follows - looking at the Reminder column for today's date and then sending an automated message to Teams:

Andrée Starå

@Raquel Love

Happy to help!

Strange! It should work indefinitely!

The bolded part always makes the reminder trigger in the current year.

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

How are you testing if it works now?

Raquel Love

@Andrée Starå - The automation is working now, so I'm not sure how to test whether it's going to work when the calendar changes to 2023. Do you need to open the sheet in order to trigger the formula in the Reminder column to switch over to the current year? What I noticed was the birthdays that took place at the beginning of January didn't result in any birthday alerts - and when I opened the sheet on January 4 after the holiday break, the alerts started up again.

Andrée Starå

@Raquel Love

Excellent!

Yes, the sheet has to be "activated" for it to trigger.

Please have a look at my post below with a method I developed to update the sheet(s) daily.

More info: 

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

✅Remember! 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!