How to add time (in years) to a date and time column.

Question

I am trying to create calculated column in a document library where a person assigns a task to another person and set next review date.

There are two columns: 'Time' which is a drop down column that has value (which have to be in year) like 1, 2, 3, 4, 5; the Second column is next 'inspection date' which is a calculated column where I add Time + Today date.

The problem is when a person select value from 'time' column it adds to day field rather than in year. For example If some select 3 from time column. the next column changes to 14/10/2016 from present date 11/10/2016. I want results should be 11/10/2019

Answer 1

Try the formula =Today + Time*365 any integer addition to the Today variable will be in days, so to get a year added to your column, you must multiply by day's in a year.

Answer 2

Instead of using [Today], which shifts every day, you might want to consider using the [Created] column. It would be bad if you used [Today] and it was December 31st, 2021, and you applied for the review to happen in a year (December 31, 2022), but then when you came back to work after New Year's, that new review date is now December 31, 2023 (because it is now January 2, 2022 and the year is now 2022). Microsoft removed the ability to use [Today] in calculated fields for this very reason.

Better would be to use [Created] as your jump point. It is a fixed reference.

You could add years to [Created] like this:

=CONCATENATE(MONTH(Created),"/",DAY(Created),"/",(TEXT(Created,"yyyy")+Time))

This works if you display it as a single line of text field. Probably date, too, but I didn't try it.