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.



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"
  • 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. Aug 26, 2020 at 16:59

1 Answer 1


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


=IF([Report Completed]=TRUE,"No",IF(ISBLANK([Report Completed Date]),"n/a",IF(AND([Report Due Date]>TODAY(),[Report Completed]=FALSE),"Yes")))
  • 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, 2020 at 13:58
  • I have updated my answer, check if it works for you. Aug 28, 2020 at 10:01
  • Thanks Michael, that helped a lot!
    – RobC_CTL
    Aug 28, 2020 at 12:00

Your Answer

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

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