Return a red icon when due date is at risk - almost there, but getting a "0" entry

Hi Folks - probably super easy, but am using a formula to return a red icon if % complete isn't 100% and if current date is 7 days out form target finish date. I am almost there, but I get a "0" when the criteria has been met (100% complete has been achieved). I would prefer a blank cell. Here is the formula I am using:

=IF([% Complete]@row = 1, 0, IF([Target Finish Date]@row = "", "", IF([Target Finish Date]@row - TODAY() <= 7, "Red", 0)))

As said above, once 100% has been marked off, I then get a "0" instead of a blank cell.

Any solutions? Thanks!

Answers

  • Kerry St. Thomas
    Kerry St. Thomas ✭✭✭✭✭✭

    The reason you're getting 0 for 100% complete is at the beginning of your formula. Change it from 0 to "", and you'll get blanks instead of 0 when % Complete is 100%.

    =IF([% Complete]@row =1, "", …

    Note that the 0 close to the end of the formula is likely to also make everything that is NOT complete and that has a target date that is NOT within 7 days is going to also show 0.

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

  • Yasssss! Thank you so much! Works perfectly!

  • One more question if I may - how do I set the formula to reference the % complete when I use symbols (e.g., the % bars) vs. just typing in 50% etc. The formula works when I just type in the percentage but does not work when I try to use symbols. Thank you!

  • Never mind, I figured it out. Use the word "Full" (with the quotes) in the formula instead of "1" which represents the filled in blue bar that represents 100%.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!