Dates to populate values in associated cells
I'm working on a training sheet where every year you work its worth a certain amount of points. What I would like to achieve is after the date (1) is repeated annually, the "Years Served" (2) column adds +1. As this is done the "Year" column (3) is updated to a certain value of points based off of "Years Served"
I believe all of this can be done in automation but after trying countless times I'm failing to achieve this. Any help would be greatly appreciated.
(Attached is the example of the sheet I am using)
Answers
-
Hi @tylero
For your "Years Served":
=ROUNDDOWN((TODAY() - [Start Date]@row) / 365.25)
The .25 is to account for Leap years, but you can leave this out if you wish.
For the points based on years earned, what are you using to calculate the amount earned?
-
@Nick Korna Thanks for the help I'll try that.
The points based on years is as follows:
Year 1 = 1 Point
Year 2 = 3
Year 3 = 5
Year 4 = 9
Year 5 = 13
Year 6 = 18
Year 7 = 21
Year 8 = 23
Year 9 = 24
Year 10 (Cap) = 25
So, as you can see it's not as simple as a +1 equation, more like once that year is struck then value will need updated.
Thanks for your help thus far!
-
I figured out the code to automate this. Code is as follows:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!