Recurrent Reminder for anniversaries and Birthdays - old dates

Nelia Peirotti

Hi there,

I set up a spreadsheet with all our employees' details. Now I need to set up recurrent reminders for Date of Births and Anniversaries, but obviously, being old dates they are not considered.

Is there any way where this can be done? 

Thanks

Kennedy Stomps

Nelia--what luck! My colleague Kate actually came up with a formula to help accomplish this a few months ago.

 

To set up this formula, you'll need a date column in your sheet that contains the birthday date and another date column to contain the formula-generated reminder date. The formula will go in the "Reminder Date" column:

 

=IF(DATE(YEAR(TODAY()), MONTH(Birthday1), DAY(Birthday1)) > TODAY(), DATE(YEAR(TODAY()), MONTH(Birthday1), DAY(Birthday1)), DATE(YEAR(TODAY()) + 1, MONTH(Birthday1), DAY(Birthday1)))

 

This formula will take the month and day you've entered in the birthday column but add the current year (or the current year + 1, if the birthday has already occurred in this year), which will make it possible to set up a reminder for the upcoming birthday. You'll just want to change the "Birthday1" references in the formula to match the column in your sheet that includes the employee birthdays, then you can set up a Reminder on the "Reminder Date" column.

Nelia Peirotti

Hi Kennedy,

 

thank you so much for your answer, but it doesn't work :-(  It says MISSING OR INVALID PARAMETER

Kennedy Stomps

Hi Nelia -- I know we're troubleshooting this over email, but in case any other users are looking to use this formula in their sheets, it's important to note that the original formula I provided assumes you already have birthday values inserted in your sheets. If you want to add this formula to a sheet where you don't already have birthday values inserted, you'll want to add an ISBLANK operator to prevent the formula from returning an error before values are added in the "Birthday" column:

 

=IF(NOT(ISBLANK(Birthday1)), IF(DATE(YEAR(TODAY()), MONTH(Birthday1), DAY(Birthday1)) > TODAY(), DATE(YEAR(TODAY()), MONTH(Birthday1), DAY(Birthday1)), DATE(YEAR(TODAY()) + 1, MONTH(Birthday1), DAY(Birthday1))))

LG

Hi, I need exactly this - the ability to have recurring reminders each year on birthdates for my clients... I have spent some time trying to make the formula provided here work but cannot achieve success... Can someone have a look at my smartsheet and assist in making this work? 

lrmerlino88886

I'm curious if I can adjust this formula and return a list of names of employees who's birthdays fall in the current month?