Looking for an easy formula to calculate anniversary off of date column
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
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!
Best Answer
-
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
Answers
-
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
-
Hi @ericncarr . That makes sense, but unfortunately, I'm getting an #Invalid Column Value error
-
@Dan Tanner did you make both columns Date columns? If not, try that.
-
@ericncarr Did the trick. Brilliant. Thank you so much.
-
@Dan Tanner You're very welcome!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!