Multiple IF statements (or similar) to drive RYGB

Wade in NZ

I am trying to write a formula that returns a Red/Yellow/Green based on 4 part. I've searched the community but nothing has helped so far. I've tried IF AND and IF INDEX formulas but I'm clearly getting something wrong in the logic or expression usage. UNPARSEABLE is my constant friend...

The example is for a project and I want to return an RYGB based on the Status, the Start Date and/or the End Date.

In descriptive form I want the formula to,

Return Blue if the Status = Not Started and Start Date > TODAY(), return Yellow if the Status = Not Started and Start Date is < TODAY(), Return Green if Status = In Progress and End Date < TODAY or Status = Completed, Red when Status <> Complete and End Date <= TODAY(-1)

My sheet is more or less like this:

I've been messing around with a variety of formula so if here is a better way I am completely open to suggestions...

Cheers

heyjay

=IF(AND(Status@row = "NOT STARTED", [Start Date]@row > TODAY()), "BLUE", IF(AND(Status@row = "NOT STARTED", [Start Date]@row < TODAY()), "YELLOW", IF(AND(Status@row = "IN PROGRESS", [Start Date]@row < TODAY()), "GREEN", IF(Status@row = "COMPLETED", "GREEN", IF(AND(Status@row <> "IN PROGRESS", [End Date]@row < TODAY(-1)), "RED")))))

Genevieve P.

Hi @Wade in NZ

This is exactly what your "Green" rule is looking for... if the task is "in progress" and the Start date is in the past:

IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",

What I would suggest doing is move your end "Red" statement up earlier, so it first checks the end date of the In Progress tasks before checking the start date.

This should be higher in the order of operations:

IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red"))))))

Try:

=IF(AND(Status@row = "Not Started", [Target Start Date]@row > TODAY()), "Gray",

IF(AND(Status@row = "Not Started", [Target End Date]@row < TODAY()), "Red",

IF(AND(Status@row = "Not Started", [Target Start Date]@row < TODAY()), "Yellow",

IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red",

IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",

IF(Status@row = "Completed", "Green"))))))

Cheers,

Genevieve

Paul Newcome

Try something like this:

IF(OR(Status@row = "Completed", "Green", IF([End Date]@row < TODAY(), "Red", IF(Status@row = "Not Started", IF([Start Date]@row>= TODAY(), "Blue", "Yellow")), "Green"))

heyjay

=IF(AND(Status@row = "NOT STARTED", [Start Date]@row > TODAY()), "BLUE", IF(AND(Status@row = "NOT STARTED", [Start Date]@row < TODAY()), "YELLOW", IF(AND(Status@row = "IN PROGRESS", [Start Date]@row < TODAY()), "GREEN", IF(Status@row = "COMPLETED", "GREEN", IF(AND(Status@row <> "IN PROGRESS", [End Date]@row < TODAY(-1)), "RED")))))

Wade in NZ

https://community.smartsheet.com/discussion/comment/328924#Comment_328924

Awesome! Many thanks! Worked a treat!

Wade in NZ

Run into a slight snag when I implemented this formula in a project we knew to be going a bit sideways. My final formula is below and unfortunately it returns a Green when the task in In Progress but the Target Start Date is < TODAY and therefore overdue. The Overdue report picked up the task but, with the Schedule Health still showing Green, it is causing confusion. Somewhere my logic hierarchy is falling over...

=IF(AND(Status@row = "Not Started", [Target Start Date]@row > TODAY()), "Gray",

IF(AND(Status@row = "Not Started", [Target End Date]@row < TODAY()), "Red",

IF(AND(Status@row = "Not Started", [Target Start Date]@row < TODAY()), "Yellow",

IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",

IF(Status@row = "Completed", "Green",

IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red"))))))

Genevieve P.

Hi @Wade in NZ

This is exactly what your "Green" rule is looking for... if the task is "in progress" and the Start date is in the past:

IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",

What I would suggest doing is move your end "Red" statement up earlier, so it first checks the end date of the In Progress tasks before checking the start date.

This should be higher in the order of operations:

IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red"))))))

Try:

=IF(AND(Status@row = "Not Started", [Target Start Date]@row > TODAY()), "Gray",

IF(AND(Status@row = "Not Started", [Target End Date]@row < TODAY()), "Red",

IF(AND(Status@row = "Not Started", [Target Start Date]@row < TODAY()), "Yellow",

IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red",

IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",

IF(Status@row = "Completed", "Green"))))))

Cheers,

Genevieve