Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Recurrent Reminder for anniversaries and Birthdays - old dates


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? 



  • Kennedy Stomps
    Kennedy Stomps Employee
    edited 05/17/16

    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
    edited 05/24/16

    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?

This discussion has been closed.