Show Red Ball if Past Due

M. David

I have a formula in the "Health" column of a sheet. This formula shows a colored ball depending on the "Status" of another column. The "Status" column is determined by another column, "% Complete". Ultimately, the part of these formulas not working is that I want it to be a Red Ball if the End Date is past and it has not been marked Complete.

Status Formula:

=IF([% Complete]@row <= 0.01, "Not Started", IF(AND([% Complete]@row > 0.01, [% Complete]@row < 0.99), "In Progress", IF([% Complete]@row >= 0.99, "Complete", "")))

Health Formula:

=IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Complete", "Green", IF(AND([End Date]@row < TODAY(), [% Complete]@row <> 1, "Red")))))

In the above pic, Can somebody explain what is wrong with these formulas and make suggestions to fix? All help is appreciated. Thanks.

Genevieve P.

Hi @M. David

Ok lets break it out and have two statements then... we can start with just checking the End Date to see if it's blank. If it IS blank, return Gray.

If there's a Date, then it will check to see if that date is in the past and if the % Complete isn't 100%. Only after these first two statements are checked will it then look at the Status:

=IF([End Date]@row = "", "Gray", IF(AND([End Date]@row < TODAY(), [% Complete]@row <> 1), "Red", IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Complete", "Green")))))

Let me know if this one did the trick!

Genevieve

Genevieve P.

Hi @M. David

IF statements stop as soon as a criteria is met. This means that the order of your statements make a difference.

You'll want to put the Red statement at the beginning, so it's read first before the Yellow statement. You also want to close off an AND() before telling it what colour to produce.

Try:

=IF(AND([End Date]@row < TODAY(), [% Complete]@row <> 1), "Red", IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Complete", "Green"))))

Let me know if this works for you!

Cheers,

Genevieve

M. David

Thanks for the help. You suggestion did fix the issue with the Red Ball that I identified. The only issue with it now is that if there is a row with no Start or End Dates, then the Status defaults to Not Started, but the Health column defaults to Red Ball. Is there a way to make the default the Gray Ball? Thanks.

Genevieve P.

Hi @M. David

Blank date cells are seen as "in the past", which is why you're getting the red ball. You can swap around the order of your statements to change up what the formula will look for first!

Try:

=IF(Status@row = "Not Started", "Gray", IF(AND([End Date]@row < TODAY(), [% Complete]@row <> 1), "Red", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Complete", "Green"))))

Cheers,

Genevieve

M. David

Genevieve,

That solved that specific issue but created a new one. If I have something overdue (End Date in the past), and it still has the words "Not Started" in the Status column, then it shows a Gray Ball instead of Red. It hits that fist item you bolded above, and stops at that step. After I change it to "In Progress", then it recognizes that it is in the past and marks it Red instead of Yellow (normal In Progress color). I think I can live with this latest suggestion, though it'd be nice if anything past the End Date could be Red Ball regardless of it has been started or not. If you think of something, let me know.

Thanks.

Genevieve P.

Hi @M. David

Ok lets break it out and have two statements then... we can start with just checking the End Date to see if it's blank. If it IS blank, return Gray.

If there's a Date, then it will check to see if that date is in the past and if the % Complete isn't 100%. Only after these first two statements are checked will it then look at the Status:

=IF([End Date]@row = "", "Gray", IF(AND([End Date]@row < TODAY(), [% Complete]@row <> 1), "Red", IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Complete", "Green")))))

Let me know if this one did the trick!

Genevieve

M. David

Genevieve,

That works great. I didn't think about splitting that first part out. Thanks for your help with this. It will come in handy with this current project and I'm sure into the future with other projects.

Thanks.

Genevieve P.

No problem! I'm glad we got there in the end 🙂