2

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

2 Answers 2

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.

1
  • They got rid of [Today] as an option, so now it's NOW(), or probably more preferable to use the [Created] date to add onto, so that the value doesn't change every day, but is based on a stagnant reference.
    – vapcguy
    Nov 3, 2021 at 16:40
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.