IF(And/OR) Formula Help

Options
Maria Watters
Maria Watters ✭✭✭✭✭
edited 08/24/22 in Formulas and Functions

Hello,

I have the below formula I am working from and everything works EXCEPT the last part of the formula where we are asking for the health to show as blank if the start date is blank or the status is Not started.

Also, we were using If(AND prior to using IF(OR and wondering what the true difference is. If we use "OR" is it saying if the status is "In Progress" OR the End Date is greater than 10 days then it's green. If that is the case I suppose we would want to go back to AND.

=IF(OR(Status@row = "In Progress", [End Date]@row - TODAY() > 10), "Green", IF(OR(Status@row = "In Progress", [End Date]@row < TODAY(11), [End Date]@row >= TODAY(3)), "Yellow", IF(OR(Status@row = "In Progress", [End Date]@row - TODAY() < 3), "Red", IF(OR(Status@row = "Complete", "Blue", IF(OR(Status@row = "Not Started", [End Date]@row - TODAY() > 29), "Green", IF(OR(Status@row = "Not Started", [End Date]@row - TODAY() < 11), "Red", IF(OR(Status@row = "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow", IF(OR(Status@row - "Not Started", [Start Date]@row, "", ""))))))))))

Tags:

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    edited 08/25/22
    Options

    Replace your typo - with = in your last IF to check Status@row = "Not Started" and also your closing bracket for OR is misplaced

    Replace IF(OR(Status@row - "Not Started", [Start Date]@row, "", "") to

    IF(OR(Status@row = "Not Started", ISBLANK([Start Date]@row)), "") 
    

    In fact, after checking you entire formula also noticed that you have incorrect brackets, rather mistyped OR for the Status@row = "Complete" condition.

    Try this as the entire formula,

    =IF(OR(Status@row = "In Progress", [End Date]@row - TODAY() > 10), "Green", 
        IF(OR(Status@row = "In Progress", [End Date]@row < TODAY(11), [End Date]@row >= TODAY(3)), "Yellow", 
            IF(OR(Status@row = "In Progress", [End Date]@row - TODAY() < 3), "Red", 
                IF(Status@row = "Complete", "Blue", 
                    IF(OR(Status@row = "Not Started", [End Date]@row - TODAY() > 29), "Green", 
                        IF(OR(Status@row = "Not Started", [End Date]@row - TODAY() < 11), "Red", 
                            IF(OR(Status@row = "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow", 
                                IF(OR(Status@row = "Not Started", ISBLANK([Start Date]@row)),""
                                )
                            )
                        )
                    )
                )
            )
        )
    )
    

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!