IF(And/OR) Formula Help

Maria Watters

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, "", ""))))))))))

Sameer Karkhanis

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)),""
                            )
                        )
                    )
                )
            )
        )
    )
)