How do i write the following formula

I am trying to write a If-nested formulas that works out the following.

What i am trying to say with this formula is the following:

IF the status is Complete and the planned end date is today or within five working days = Green

IF the status is In Progress and the planned end date is today or with in three working days = Yellow

IF the status is Not Started and the planned end date is today or with in 10 days after todays date = Red

=If(OR(Status@row = "Complete", [due date]@row =< Today(-5)), "Green", IF(Status@row = "In Progress", [due date]@row < Today(-3)) "Yellow", IF(Status@row = "Not Started", [Planned Finishe] = Today(10)) "Red"

Is there a AND function in here, or is what i am trying to ask not correct.... Please help

Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To write for your exact criteria listed, you would use the AND function within your nested IF's. Wherever you have written out "and", you will use an AND function to combine those two criteria.

    So to pull from the criteria you have listed in your formula...

    AND(Status@row = "Complete", [Due Date]@row <= TODAY(-5)) --> "Green"

    AND(Status@row = "In Progress", [Due Date]@row< TODAY(-3)) --> "Yellow"

    AND(Status@row = "Not Started", [Due Date]@row = TODAY(10)) --> "Red"

    =IF(AND(Status@row = "Complete", [Due Date]@row <= TODAY(-5)), "Green", IF(AND(Status@row = "In Progress", [Due Date]@row< TODAY(-3)), "Yellow", IF(AND(Status@row = "Not Started", [Due Date]@row = TODAY(10)), "Red")))

    Keep in mind that the above may not work as expected. The criteria set forth in your formula does not match the criteria you have written out and both leave a lot of different scenarios unaccounted for. The above just shows the proper syntax for using the AND function within a nested IF.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!