Calculated Columns - Comparing multiple columns with IF statement

Question

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"

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

Updated:

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