Status RGYB balls formula, when due tomorrow and past due

I built out this formula to change the status marker balls colors based on today's date and my "due Date" column:

=IF(Status@row <> "Complete", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row > TODAY(2), "Yellow", IF([Completion/ Posting Date]@row >= TODAY(1), "Red")))))

But I can't get it to keep the status as red of anything that is due tomorrow, today, or was due any time in the past if it isn't marked as complete. So if an action item was due 3 days ago and isn't done=red, if it's due tomorrow and isn't done=red, if due today=red. Basically, anything with a date that is due soon or should've been done already should remain red in the marker ball column.

The other colors work as desired.

Tags:

Best Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    edited 03/31/21 Answer ✓

    =IF(status@row <> "Complete", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row > TODAY(2), "Yellow", IF([Completion/ Posting Date]@row >= TODAY(1), "Red", IF([Completion/ Posting Date]@row <= TODAY(), "RED"))))))

    I added the last bit - IF([Completion/ Posting Date]@row <= TODAY(), "RED"

  • Guy Behanna
    Guy Behanna ✭✭✭✭
    Answer ✓

    @Sarah Daley

    I adjusted your formula to address your first question and then added another "IF" statement to address your second question:

    =IF([Completion/ Posting Date]@row = "", "", IF(Status@row <> "Complete", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row >= TODAY(2), "Yellow", "Red")))))

    Basically, the formula will not show a ball if the Completion/Posting Date is blank and will then show a red ball for anything not meeting the criteria for blue, green and yellow.

    Hopefully, this will solve your problems.

    Good luck!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try rearranging the order of your IF statements so that the IF outputting "Red" comes before the rest of the other colors.

    =IF(Status@row <> "Complete", IF([Completion/ Posting Date]@row >= TODAY(1), "Red", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row > TODAY(2), "Yellow")))))

  • James Keuning
    James Keuning ✭✭✭✭✭
    edited 03/31/21 Answer ✓

    =IF(status@row <> "Complete", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row > TODAY(2), "Yellow", IF([Completion/ Posting Date]@row >= TODAY(1), "Red", IF([Completion/ Posting Date]@row <= TODAY(), "RED"))))))

    I added the last bit - IF([Completion/ Posting Date]@row <= TODAY(), "RED"

  • Sarah Daley
    Sarah Daley ✭✭✭
    edited 03/31/21

    Hmm seems to turn everything that had a ball red. anything without a ball even if it had a date didn't change. I did make it a column formula.

  • Sarah Daley
    Sarah Daley ✭✭✭

    Sorry one more thing, If I wanted to exclude any cells from getting a bubble if the Completion/ Posting Date is blank, I can't seem to get that into the formula. I'm still pretty new to formulas.

  • Guy Behanna
    Guy Behanna ✭✭✭✭
    Answer ✓

    @Sarah Daley

    I adjusted your formula to address your first question and then added another "IF" statement to address your second question:

    =IF([Completion/ Posting Date]@row = "", "", IF(Status@row <> "Complete", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row >= TODAY(2), "Yellow", "Red")))))

    Basically, the formula will not show a ball if the Completion/Posting Date is blank and will then show a red ball for anything not meeting the criteria for blue, green and yellow.

    Hopefully, this will solve your problems.

    Good luck!

  • Sarah Daley
    Sarah Daley ✭✭✭

    @Guy Behanna Thank you that worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!