Status RGYB balls formula, when due tomorrow and past due

Sarah Daley

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.

James Keuning

=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

@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!

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

=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

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

Thank you @James Keuning that worked!

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

@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

@Guy Behanna Thank you that worked!