0

I am fighting with SharePoint calculated columns and trying to compare multiple columns.

The SharePoint list has the following columns:

  • Completed Date - Date
  • Completed - Yes/No
  • Due Date - Date
  • TodayDate - Date
  • Overdue - Calculated

I am trying to do the following:

  • When "Completed Date" is blank "Overdue" value = "n/a"
  • When "Due Date" is greater than "TodaysDate" the "Overdue" value = "Yes"
  • When "Completed" equals TRUE the "Overdue" value = "No"

I've got this far, but the "Yes" part isn't working

=IF(AND([Report Completed]=TRUE),"No",IF(OR(ISBLANK([Report Completed Date])),"n/a",IF(AND([Report Due Date]>Today),"Yes")))

Any help gratefully received.

Rob

UPDATE

I just noticed I missed part of the "When "Due Date" is greater than "TodaysDate" the "Overdue" value = "Yes" condition. It should have been this:

  • When "Due Date" is greater than "TodaysDate" AND "Completed" = FALSE the "Overdue" value = "Yes"
1
  • Try using this - IF(ISBLANK([Report Completed Date]),"n/a",IF([Report Due Date]>Today),"Yes",IF([Report Completed]=TRUE,"No")) and let me know if it works. – Diksha Poddar Aug 26 '20 at 16:59
0

To get the Today date in calculated column, you should use Today() instead of Today.

Besides, you don't have to use AND(),OR() function to wrap your condition. This should work for you:

=IF([Report Completed]=TRUE,"No",IF(ISBLANK([Report Completed Date]),"n/a",IF([Report Due Date]>TODAY(),"Yes")))

Updated:

=IF([Report Completed]=TRUE,"No",IF(ISBLANK([Report Completed Date]),"n/a",IF(AND([Report Due Date]>TODAY(),[Report Completed]=FALSE),"Yes")))
3
  • Thanks Michael - your comment about removing AND/OR function made me realise I had mis-typed one of the conditions, so I've updated the thread with correct condition requirement. – RobC_CTL Aug 27 '20 at 13:58
  • I have updated my answer, check if it works for you. – Michael Han_MSFT Aug 28 '20 at 10:01
  • Thanks Michael, that helped a lot! – RobC_CTL Aug 28 '20 at 12:00

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.