Calculate years between today and manually input year
I would like to calculate years of experience using today's date so that the output automatically updates when the year changes. The following formula does so successfully: =YEAR(TODAY(0)) - 1974. This results in a calculation of 49 since 2023 - 1974 = 49. And, when the year changes to 2024, and beyond, the output will also automatically update because of the TODAY function.
Is it possible to setup a sheet in such a way that the formula is locked in place and the user only inputs the year? For example, the user clicks on the cell and enters "1974", which then runs through the formula.
Thanks.
Answers
-
You would replace the hardcoded 1974 with a cell reference.
=YEAR(TODAY()) - [Column name]@row
-
Hi @Paul Newcome, I had considered this but hope there might be another solution since this requires the creation of an additional column in an already crowded sheet. Any other ideas?
-
The only other option would be that they update the formula itself which opens you up to a whole lot of potential issues.
-
Right. Thanks for your help, @Paul Newcome.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!