Date formula to show when retraining is due based on number of months and/or year(s).
I'd like to be able to use a formula to add either 1 year, 3 years, 5 years etc. to show when retraining is due based on the Training Completion Date.
Any suggestions on how to approach or a date type formula that will add one, three, or 5 years to the Training Completion Date would be greatly appreciated.
Answers
-
Your training completion date column and the retraining date column should both be Date format.
The simplest option is to assume a year is 365 days. Then you can use this formula in the retraining date column to add 365 days to the Training Completion Date
=[Training Completion Date]@row + 365
Or 1095 for 3 years, etc.
But if you want to be more accurate, which could be important for your 5 year renewals where there will definitely be at least one leap year, you can create a date based on elements from the Training Completion Date, using the DATE function like this:
=DATE(YEAR([Training Completion Date]@row) + 1, MONTH([Training Completion Date]@row), DAY([Training Completion Date]@row))
Change the +1 to +3 or +5 as appropriate.
This function creates a date where the day and month are the same as the Training Completion Date and the year is the year of the Training Completion Date plus whatever number of years you enter.
You could also put the number of years to add in another cell and refer to that instead of manually changing the +1. You would do that by referencing the cell.
=DATE(YEAR([Training Completion Date]@row) + [Year to add]@row, MONTH([Training Completion Date]@row), DAY([Training Completion Date]@row))
-
Hey @Robert Groves
The actual formula will depend on how you wish the 1,3 or 5 to be entered into the calculation. You could have a dropdown column that does the selection of 1,3 or 5. Or you could have a nested IF that does the calculation.
In the ReTrain Date column:
*Assuming a dropdown column - you'll have to edit formula with your actual column name(s)
=DATE(YEAR([Training Completion Date]@row)+[Dropdown column]@row,MONTH([Training Completion Date]@row),DAY([Training Completion Date]@row))
*Nested IF (if trigger value is numeric instead of a textstring do NOT enclose that value in quotes. Only enclose words/textstrings in quotes)
=IF([whatever field]@row ="some textstringA", DATE(YEAR([Training Completion Date]@row)+1,MONTH([Training Completion Date]@row),DAY([Training Completion Date]@row)), IF([whatever field]@row ="some textstringB", DATE(YEAR([Training Completion Date]@row)+3,MONTH([Training Completion Date]@row),DAY([Training Completion Date]@row)), IF([whatever field]@row ="some textstringC", DATE(YEAR([Training Completion Date]@row)+5,MONTH([Training Completion Date]@row),DAY([Training Completion Date]@row)))))
Will either of these work for you?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!