Looking for an easy formula to calculate anniversary off of date column

Dan Tanner

Hi everyone,

Wondering if anyone has a quick and easy formula to calculate an anniversary off of a date column?

datequestion.png

Photo attached. Essentially, I'm looking for the 2022 Anniversary column to show

1/05/22

1/07/22

1/24/22

etc

I understand if this might not be possible in 2022 since dates have passed, but we're making columns to for the next few years. Is there any formula that can do this? The last time I did a sheet like this, I just copied and pasted the hire date column into the 2022 column and changed the year, but this sheet is over 300 lines, so hoping I won't have to do this manually 😂 Any help is appreciated!

ericncarr

Hello,

This will basically construct a date by grabbing the month and day from your "Hire Date" column and then just uses 2022 as the year.

=DATE(2022, MONTH([Hire Date]@row), DAY([Hire Date]@row))

For 2023 just change 2022 to 2023, so on and so forth. Does that work? @Dan Tanner

ericncarr

Hello,

This will basically construct a date by grabbing the month and day from your "Hire Date" column and then just uses 2022 as the year.

=DATE(2022, MONTH([Hire Date]@row), DAY([Hire Date]@row))

For 2023 just change 2022 to 2023, so on and so forth. Does that work? @Dan Tanner

Dan Tanner

Hi @ericncarr . That makes sense, but unfortunately, I'm getting an #Invalid Column Value error

ericncarr

@Dan Tanner did you make both columns Date columns? If not, try that.

Dan Tanner

@ericncarr Did the trick. Brilliant. Thank you so much.

ericncarr

@Dan Tanner You're very welcome!