# RYG Status column, IF/AND/OR formula help

Options
Employee
edited 12/09/19

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

Tags:

• ✭✭✭✭✭✭
edited 01/31/19
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Employee
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!