RYG Status column, IF/AND/OR formula help

Eric D

Hello Community,

I'm trying to establish an RYG Status column formula in my sheet based on values in three other columns. One is a STATUS column with drop-down selections of Completed, Not Started, In Progress. The other two are date columns START DATE, FINISH DATE. 

Here's the statements without proper syntax: 

IF(STATUS)= Completed, Green

IF(STATUS)= BLANK AND (START DATE)>TODAY, BLANK(no symbol)

IF(STATUS)= NOT STARTED AND (START DATE) <TODAY, OR IF(STATUS) = IN PROGRESS AND (FINISH DATE) <TODAY, RED

IF(STATUS) = IN PROGRESS AND (START DATE) < TODAY AND (FINISH DATE) > TODAY, YELLOW

Putting all these in one statement has me baffled.  I've tried this but am getting #UNPARSEABLE error

=IF(AND(STATUS@row = "Not Started", ([START DATE]@row < TODAY()), OR(STATUS@row = "In Progress", ([FINISH DATE]@row > TODAY())), "Red"), IF(AND([STATUS]@row = "In Progress", OR([START DATE]@row < TODAY(), [FINISH DATE]@row > TODAY())), "Yellow"), IF(ISBLANK([STATUS]@row), ""), IF([STATUS]@row = "Completed", "Green"))))

I feel like my order of operations is incorrect as is something in the syntax. 

Any help would be greatly appreciated.  

Thanks

Eric

 

 

Andrée Starå

Hi Eric,

Try this.

I removed the Blank part because I don't think you need it. 

IF(STATUS)= BLANK AND (START DATE)>TODAY, BLANK(no symbol)

The below formula could probably be even more simplified.

=IF(STATUS@row = "Completed"; "Green"; IF(AND(STATUS@row = "In Progress"; [Start Date]@row < TODAY(); [Finish Date]@row > TODAY()); "Yellow"; IF(OR(AND(STATUS@row = "Not Started"); AND([Start Date]@row < TODAY(); OR(AND(STATUS@row = "In Progress"; [Finish Date]@row < TODAY())))); "Red")))    

The same version but with the below changes for your and others convenience.  

=IF(STATUS@row = "Completed", "Green", IF(AND(STATUS@row = "In Progress", [Start Date]@row < TODAY(), [Finish Date]@row > TODAY()), "Yellow", IF(OR(AND(STATUS@row = "Not Started"), AND([Start Date]@row < TODAY(), OR(AND(STATUS@row = "In Progress", [Finish Date]@row < TODAY())))), "Red")))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Eric D

Hello Andrée,

YES! Works fantastic. I see now the proper order of operations and syntax and the omission of ISBLANK makes perfect sense. I can't thank you enough.

Have a wonderful day!

Regards,

Eric