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

Rich G

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.

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.

Paul Newcome

I don't see anything in your formula referencing the actual revised date. Give this a try:

=IF(AND([Finding Status]@row <> "Compliance Finding is Closed", [Finding Status]@row <> "Advisory Recommendation is Closed", [Finding Status]@row <> "Advisory Recommendation Not Accepted by Subrecipient", IF(ISDATE([Revised Compliance Remedy Deadline]@row), [Revised Compliance Remedy Deadline]@row, [Compliance Remedy Deadline]@row)< TODAY()), 1)

Paul Newcome

I don't see anything in your formula referencing the actual revised date. Give this a try:

=IF(AND([Finding Status]@row <> "Compliance Finding is Closed", [Finding Status]@row <> "Advisory Recommendation is Closed", [Finding Status]@row <> "Advisory Recommendation Not Accepted by Subrecipient", IF(ISDATE([Revised Compliance Remedy Deadline]@row), [Revised Compliance Remedy Deadline]@row, [Compliance Remedy Deadline]@row)< TODAY()), 1)

Rich G

Your formula is working on all rows; I am most grateful for your assistance. I was so bogged down with each condition in the status column, it did not occur to me to simplify the formula by using <> instead of =. I am not familiar with ISDATE function so i will study that item. You have been of great help, again, thanks.

Paul Newcome

Happy to help. 👍️

The ISDATE function will simply output a true or false value based on whether or not the data being evaluated is a date type value. it is the same concept for ISNUMBER, ISTEXT, ISODD, ISEVEN, and ISERROR. It quite literally is a yes/no for "Is it this?"