How do i write the following formula

SharonR

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

ron.judenberg112096

You have a lot of missing cases. For example, what should the status be if it's 11+ days away but not started, etc.

Also, I'm not sure why the end date matters if it's Complete

I think what you want is (assumes only 3 possible statuses)

IF(Status@row = "Not Started", [due date] <= Today(10), "Red"),

IF(Status@row = "In Progress", [due date]@row < Today(3), "Yellow"),"Green")

ron.judenberg112096

You have a lot of missing cases. For example, what should the status be if it's 11+ days away but not started, etc.

Also, I'm not sure why the end date matters if it's Complete

I think what you want is (assumes only 3 possible statuses)

IF(Status@row = "Not Started", [due date] <= Today(10), "Red"),

IF(Status@row = "In Progress", [due date]@row < Today(3), "Yellow"),"Green")

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.