Date formula to show when retraining is due based on number of months and/or year(s).

Robert Groves

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. 

KPH

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))

Kelly Moore

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