Help with RYGB Formula for Days past Start Date, Blue = Complete Status from Multi Select

HarveyL

Hello Smartsheets Friends,

Using a Smartsheet for keeping track of Activation Status. Here is what I am hoping to achieve.

1-7 Days from Handover Date = Activation Call Status - Green

8-14 Days from Handover Date = Activation Call Status - Yellow

15+ Days from Handover Date = Activation Call Status - Red

When customer Status is selected (from Multi Select options below, currently 5th from top) Complete - Blue

A bonus would be incorporating workdays as the only days to be counted. I've tried several iterations of formulas but I keep getting unparseable. Like many early friends on Smartsheets, I have not worked with formulas at all and work in a customer-facing role. I am creating this in order to better help my team be more organized. I would greatly appreciate it if someone can recommend the best formula to use and would also appreciate the time given. Thank you, friends.

Multi Select Options order

Unresponsive

Stalled

Pending Response

In Progress

Complete (Activation complete)

N/A

HarveyL

I realized my (Activation Complete) was causing issues so I changed the option to Activation Complete.

This formula changed the Activation Call Status column blue!

=IF(CONTAINS("Activation Complete", [Customer Status]@row), "Blue")

HarveyL

Now I am trying to combine the above formula with...

I keep getting unparsable and not sure how to address the 8-14 days = yellow.

HarveyL

Another development friends.

I moved my formula to the Live Sheet and received invalid operation.

=IF(CONTAINS("Activation Complete", [Customer Status]@row), "Blue", IF(TODAY() - [Go Live/ PC Handover Date]@row > 15, "Red", IF(TODAY() - [Go Live/ PC Handover Date]@row < 7, "Green", "Yellow")))

HarveyL

Update: It appears it was just on the first cell but the rest of the column checks out!

A few things I would love your help incorporating...

-WORKDAY Function | Smartsheet Learning Center

-If not a date -> ? *I see a lot of my cells are green because there is no date. With RYGB being the only option, do you folx have any recommendations on this one?