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

Dan Tanner
Dan Tanner ✭✭✭✭
edited 09/14/22 in Formulas and Functions

Hi everyone,

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

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





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!

Best Answer

  • ericncarr
    ericncarr ✭✭✭✭✭
    edited 09/14/22 Answer ✓


    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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!