Formula to change date from existing date column to the current year?
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:
- 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.
- 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
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!