Date column triggering urgency status column

Options

Hello! I have three columns: 1. Date 2. Checkbox 3. Urgency status

If the date populated in the Date column is one week or sooner from today's date, and the checkbox is unchecked, I would like the urgency status to be yellow. If the date populated in the Date column is past today's date and the checkbox is unchecked, I would like the urgency status to be red. If the date in the Date column is more than 1 week away from today's date, regardless of checkbox, I would like the urgency status to be green. If the checkbox is checked, then the urgency status should be green, no matter what the date is.


Thank you!

Best Answer

Answers

  • Kelly Drake
    Kelly Drake Overachievers Alumni
    Options

    Try this....

    =IF(Checkbox@row = 1, "Green", IF(AND(Date@row < TODAY(), Checkbox@row = 0), "Red", IF(AND(Date@row <= TODAY(+7), Checkbox@row = 0), "Yellow", IF(Date@row > TODAY(+7), "Green"))))


    It reorders your statements just a little bit to the following:

    • If the checkbox is checked, then the urgency status should be green, no matter what the date is.
    • If the date populated in the Date column is past today's date and the checkbox is unchecked, I would like the urgency status to be red. 
    • If the date populated in the Date column is one week or sooner from today's date, and the checkbox is unchecked, I would like the urgency status to be yellow. 
    • If the date in the Date column is more than 1 week away from today's date, regardless of checkbox, I would like the urgency status to be green. 

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • Shannon Scarbrough
    Shannon Scarbrough ✭✭✭✭
    edited 06/22/21
    Options

    Hi @Kelly Drake and thanks!

    I typed in like this: =IF([Did the maintenance event occur?]@row = 1, "Green", IF(AND(Date@row < TODAY(), [Did the maintenance event occur?]@row = 0), "Red", IF(AND(Date@row <= TODAY(+7), [Did the maintenance event occur?]@row = 0), "Yellow", IF(Date@row > TODAY(+7), "Green"))))

    and am getting unparseable..

  • Shannon Scarbrough
    Options

    Hi @Paul Newcome do you have any ideas on why I might be getting an unparseable error on the above formula? Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Commas and parenthesis seem to all be in order, so the only thing I can think of right off would be column names.


    I would actually suggest a slight rewrite for a little bit of added efficiency...

    =IF(OR([Did the maintenance event occur?]@row = 1, Date@row > TODAY(7)), "Green", IF(Date@row < TODAY(), "Red", "Yellow"))


    The way this works is we go ahead and get both of the "Green" outputs out of the way since those both override any other color.

    Since we specified the checkbox being checked in the first IF, by default it must be unchecked to get past that which means we don't need to specify that for any other IF using an AND.

    Next, since we already specified one end of the date range in the "Green" output, we specify the other end in the "Red" output, and that allows us to just use the "value if false" portion for the "Yellow" output since any dates that make it past the "Red" and "Green" must be greater than today but less than today + 7.

  • Shannon Scarbrough
    Options

    Hi @Paul Newcome that worked! Thanks! Do you know if there's a way the Red, Yellow, Green symbol column can show up as blank if there is no date entered into the "Date of Expected Maintenance Event" column?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Sure. Give this a whirl...

    =IF([Date of Expected Maintenance Event]@row <> "", IF(OR([Did the maintenance event occur?]@row = 1, Date@row > TODAY(7)), "Green", IF(Date@row < TODAY(), "Red", "Yellow")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!