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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?"
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!