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!

Find more posts tagged with

Accepted answers

Kelly Moore

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

All comments

Kelly Moore

Hey @Raquel Bradley

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

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!

mbarthkelly

I just used this for a similar worksheet. We are going to use it for work milestone reminders and send the reminders out around the 25th of the preceding month. Since this will update it for the current year, is there a way to update it automatically for January milestones so they are sent in December?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.