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.
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
-
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)
Answers
-
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)
-
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.
-
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?"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!