Formula to change date from existing date column to the current year?

Options
Raquel Love
Raquel Love ✭✭✭
edited 02/05/21 in Formulas and Functions

I have setup an automation to send a Microsoft Teams message to a channel whenever it's someone's birthday - and the only way I was able to do this was by taking the birthdays in the "Date" column and making the year this year versus the actual year the team member was born (if not, I wouldn't be able to set up a "when date is reached" trigger in my automation).

Two options I thought might be possible so I don't have to keep changing the year in the "Date" column every year for the automation to work:

  1. Create a column called "Current Date" and add a formula that takes the date from the "Date" column and changes it to the current year - I could then use the Date column to house the actual birthdate and the Current Date column for my birthday announcement automation.
  2. Create an IF formula that looks at the month and day from the "Date" column and if it matches the month and day from Today's date then it adds "Yes" to another column called "Birthday" - I could then create an automation where if the "Birthday" column contains "Yes", then a birthday announcement message will go out.

Any thoughts on how I could use a formula to accomplish this birthday announcement automation would be super helpful!

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Raquel Bradley

    Yes you can change the date - I'll share one solution. ALSO, you CAN set a re-occurring message using this alert once it's set up. You'll use the Date Field that you have set up, however, instead of setting the trigger for 'Run Once' click the dropdown and choose CUSTOM. Choose the interval- in this case YEAR.


    To change the date to the current year and keep it evergreen- place this in your Helper DATE column

    =DATE(YEAR(TODAY()), MONTH([your birthday column]@row), DAY([your birthday column]@row))

    All you really though is to change this once to 2021.

    =DATE(2021, MONTH([your birthday column]@row), DAY([your birthday column]@row))

    cheers

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Raquel Bradley

    Yes you can change the date - I'll share one solution. ALSO, you CAN set a re-occurring message using this alert once it's set up. You'll use the Date Field that you have set up, however, instead of setting the trigger for 'Run Once' click the dropdown and choose CUSTOM. Choose the interval- in this case YEAR.


    To change the date to the current year and keep it evergreen- place this in your Helper DATE column

    =DATE(YEAR(TODAY()), MONTH([your birthday column]@row), DAY([your birthday column]@row))

    All you really though is to change this once to 2021.

    =DATE(2021, MONTH([your birthday column]@row), DAY([your birthday column]@row))

    cheers

    Kelly

  • Raquel Love
    Raquel Love ✭✭✭
    edited 02/05/21
    Options

    Thanks @KDM - this is perfect! I'll use the "=DATE(YEAR(TODAY()), MONTH([your birthday column]@row), DAY([your birthday column]@row))" formula in my Helper column, set my automation to run when the date in my Helper column is reached & then every year afterwards. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!