I am trying to create an If statement that looks at a date column in sharepoint and if its 11months after that date the column displays yes.

any help on this as:

=IF(DATE(YEAR([Compliance Date]),MONTH([Compliance Date])+11,DAY([Compliance Date])), "Due", "Not Due")

doesnt work?

  • Are you asking that if today > compliance date + 11 months, then it should be Due else Not Due? Calculated columns run only when item is updated. It does not compute everyday. – harshal gite Oct 13 '19 at 10:47

If you want to check if the date is due, then you could refer to the formula below.

IF(Today()>DATE(YEAR([Compliance Date]),MONTH([Compliance Date])+11,DAY([Compliance Date])),"Due","Not Due")

In your example, you have:

MONTH([Compliance Date])+11

If the month of Compliance Date is December, your formula is going to try to use 23 as the month parameter, which is invalid. You have to check for year rollover.

But keep in mind that this is only computed when the list item is added or updated. You aren’t comparing against the current date, but if you were, it would not dynamically update each day. Only on a save of the list item.

One way around that is to write a script that touches (saves) every list item every day. It’s brute force... but that’s SharePoint.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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