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

HarveyL
HarveyL ✭✭
edited 02/18/22 in Formulas and Functions

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


Answers

  • HarveyL
    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
    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
    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
    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!