Remove "In Progress" from formula, but comes back unparseable

Options

Good morning!

I would like to change the behaviour of the below formula that defines rag status...

=IF(Status@row = "Complete", "Gray", IF(AND(Status@row = "In Progress", [Due Date]@row > TODAY()), "Yellow", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row < TODAY(), "Red", "Yellow"))))


I would like to remove the behaviour that if the task is "In Progress" the Rag is set to yellow, and just want Red, Yellow, Green and Gray to trigger as per Due Date only. However when I remove the (AND(Status@row... part the box comes back as unparseable.

I seem to be missing something, please help!

Ollie

Tags:

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    @Ollie Cater

    Let's try reordering it:

    =IF(Status@row = "Complete", "Gray", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row > TODAY(), "Yellow", IF([Due Date]@row < TODAY(), "Red", "Yellow"))))

    So this reads:

    If the status is complete, Gray.

    Otherwise, if the start date is more than 7 days in the future, Green.

    Otherwise, if the due date is still in the future, Yellow.

    Otherwise, if the due date is in the past, Red.

    Otherwise, show yellow.


    You may want to change the last "yellow" to "" as a check. That way, if anything doesn't meet any of the criteria in the formula, it will show blank. Then we'd be able to troubleshoot where there are gaps in the formula, if any.

    =IF(Status@row = "Complete", "Gray", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row > TODAY(), "Yellow", IF([Due Date]@row < TODAY(), "Red", ""))))

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    Hi @Ollie Cater,

    Try this:

    =IF(Status@row = "Complete", "Gray", IF([Due Date]@row > TODAY(), "Yellow", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row < TODAY(), "Red", "Yellow"))))


    I think you just had an extra end parenthesis in there from the AND(


    Let me know if it fixes it!


    Best,

    Heather

  • Ollie Cater
    Options

    Thanks Heather.

    That doesn't throw up the error, but the rag status is still showing as yellow more that 7 days before the due date.

    Sorry, I'm fairly new to Smart Sheet.

    I just want

    1. Due date is more than 7 days away - Green
    2. Due date is 7 days away - Yellow
    3. Due date is passed - Red
    4. Status is "Complete" - Grey

    Really appreciate the assistance.

    Ollie

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    @Ollie Cater

    Let's try reordering it:

    =IF(Status@row = "Complete", "Gray", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row > TODAY(), "Yellow", IF([Due Date]@row < TODAY(), "Red", "Yellow"))))

    So this reads:

    If the status is complete, Gray.

    Otherwise, if the start date is more than 7 days in the future, Green.

    Otherwise, if the due date is still in the future, Yellow.

    Otherwise, if the due date is in the past, Red.

    Otherwise, show yellow.


    You may want to change the last "yellow" to "" as a check. That way, if anything doesn't meet any of the criteria in the formula, it will show blank. Then we'd be able to troubleshoot where there are gaps in the formula, if any.

    =IF(Status@row = "Complete", "Gray", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row > TODAY(), "Yellow", IF([Due Date]@row < TODAY(), "Red", ""))))

  • Ollie Cater
    Options

    That looks to have worked a charm! Thank you!

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    Fantastic! Glad it worked. 😀