Cannot Figure Logic or Syntax for Nested IF, AND, and OR formula

I have been unable, after 4 straight days of trying, to figure out this formula. I am a novice with Smartsheet. I need a Finding Overdue column (checkbox type) to report if a finding is overdue. Closed findings are automatically not overdue. Open findings have a deadline; however, the deadline may be extended, in which case the overdue calculation must be based on the revised remedy deadline date, not the remedy deadline date.

I have a functional formula (e.g., it is not throwing errors), but it is not accurately giving results. For example, Row 20 should be showing overdue, but is not.

Untitled-1.jpg

The logic in this Finding Overdue column:

If: If Compliance Finding is Closed  or Advisory Recommendation is Closed, or Advisory Recommendation Not Accepted, then Flag is not checked

IF Else: If Compliance Finding is Open OR Advisory Recommendation is Open AND Remedy Deadline Extension Request is FALSE, then calculate if the row is late (IF([Compliance Remedy Deadline]@row < TODAY())

Else: If Compliance Finding is Open OR Advisory Recommendation is Open AND Remedy Deadline Extension Request is TRUE, then calculate if the project is late (IF([Revised Compliance Remedy Deadline]@row < TODAY())

My formula:

=IF(OR([Finding Status]@row = "Compliance Finding is Closed", [Finding Status]@row = "Advisory Recommendation is Closed", [Finding Status]@row = "Advisory Recommendation Not Accepted by Subrecipient"), 0, IF(AND(OR([Finding Status]@row = "Compliance Finding is Open", [Finding Status]@row = "Advisory Recommendation is Open"), [Remedy Deadline Extension Request]@row = 0), IF([Compliance Remedy Deadline]@row < TODAY(), 1, IF(AND(OR([Finding Status]@row = "Compliance Finding is Open", [Finding Status]@row = "Advisory Recommendation is Open"), [Remedy Deadline Extension Request]@row = 0), IF([Compliance Remedy Deadline]@row < TODAY(), 1, 0)))))

Any help would be greatly appreciated.

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!