Health Color Bubbles Tied to % Completion & Dates

Hellow,


I am trying to take the following plan with the follow formula to follow these rules and am stuck. Disclaiming not the best at the large multi conditional formulas yet.


I use the Health Column bubbles RYGB

  • If a task has 0 % Complete and is not past Start Date, green or no health ball if possible
  • If a task has > 0 - < 100 % Complete and is not past End Date, green
  • If a task is 100% complete, blue
  • If a task has 0 % Complete and is past Start Date, yellow
  • If a task has 100 % Complete and is past End Date, red


Columns are % Complete & Start Date & End Date

Formula failing = =IF([% Complete]29 = 1, "Blue", IF([End Date]@29 < TODAY(), "Red", IF([Start Date]29 < TODAY(), IF([% Complete]29 = 0, "Yellow", "Green"), IF([% Complete]29 > 0, "Green"))))


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi Lauren,

    I'll break down each statement first, and then at the end show the full formula. This is indeed a Nested IF statement... you're just missing adding in an OR and an AND in different places. I've also changed the order of how they are displayed, since Logic formulas read left-to-right and will stop as soon as it finds a match that works.

    Oh, and I used @row instead of row references (the numbers) as this will then be valid for any row! (Click here for more information on @row)


    BLUE

    If a task is 100% complete, blue

    *Note: Putting this at the start means you don't need to add "less than 100" as criteria in other statements.

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


    YELLOW

    If a task has 0 % Complete and is past Start Date, yellow

    IF(AND([% Complete]@row = 0, [Start Date]@row < TODAY()), "Yellow",


    BLANK

    If there is no Start Date, no health ball &

    If a task has 0 % Complete and is not past Start Date, no health ball

    IF(OR([Start Date]@row = "", AND([Start Date]@row > TODAY(), [% Complete]@row = 0)), "",


    GREEN

    If a task has > 0 - < 100 % Complete and is not past End Date or no End Date identified, green

    IF(OR([End Date]@row = "", [End Date]@row > TODAY()), "Green",


    RED

    If a task has < 100 % Complete and is past End Date, red

    IF([End Date]@row < TODAY(), "Red")))))



    FULL FORMULA:

    =IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row = 0, [Start Date]@row < TODAY()), "Yellow", IF(OR([Start Date]@row = "", AND([Start Date]@row > TODAY(), [% Complete]@row = 0)), "", IF(OR([End Date]@row = "", [End Date]@row > TODAY()), "Green", IF([End Date]@row < TODAY(), "Red")))))


    Let me know if I can help explain any of the statements above, or if you have other criteria to add in.

    Cheers,

    Genevieve

Answers

  • UPDATE:

    If there is no Start Date, green or no health ball if possible

    If a task has 0 % Complete and is not past Start Date, green or no health ball if possible

    If a task has < 100 % Complete and is past End Date, red

    If a task has > 0 - < 100 % Complete and is not past End Date or no End Date identified, green

    If a task is 100% complete, blue

    If a task has 0 % Complete and is past Start Date, yellow

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi Lauren,

    I'll break down each statement first, and then at the end show the full formula. This is indeed a Nested IF statement... you're just missing adding in an OR and an AND in different places. I've also changed the order of how they are displayed, since Logic formulas read left-to-right and will stop as soon as it finds a match that works.

    Oh, and I used @row instead of row references (the numbers) as this will then be valid for any row! (Click here for more information on @row)


    BLUE

    If a task is 100% complete, blue

    *Note: Putting this at the start means you don't need to add "less than 100" as criteria in other statements.

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


    YELLOW

    If a task has 0 % Complete and is past Start Date, yellow

    IF(AND([% Complete]@row = 0, [Start Date]@row < TODAY()), "Yellow",


    BLANK

    If there is no Start Date, no health ball &

    If a task has 0 % Complete and is not past Start Date, no health ball

    IF(OR([Start Date]@row = "", AND([Start Date]@row > TODAY(), [% Complete]@row = 0)), "",


    GREEN

    If a task has > 0 - < 100 % Complete and is not past End Date or no End Date identified, green

    IF(OR([End Date]@row = "", [End Date]@row > TODAY()), "Green",


    RED

    If a task has < 100 % Complete and is past End Date, red

    IF([End Date]@row < TODAY(), "Red")))))



    FULL FORMULA:

    =IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row = 0, [Start Date]@row < TODAY()), "Yellow", IF(OR([Start Date]@row = "", AND([Start Date]@row > TODAY(), [% Complete]@row = 0)), "", IF(OR([End Date]@row = "", [End Date]@row > TODAY()), "Green", IF([End Date]@row < TODAY(), "Red")))))


    Let me know if I can help explain any of the statements above, or if you have other criteria to add in.

    Cheers,

    Genevieve

  • THIS WORKED PERFECTLY!! Also thank you so much for the breakdown. That helps me understand it much better. Also, good call on the @row. I honestly see that in the communities but thought everyone was just saying enter you row...that will make this so much easier! Thank you again!

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem at all! So glad it worked for you. 🙂

    @row is one of my favourite little functions... it essentially just says "hey, look at only THIS row" without needing to know what that row number is. It not only makes building formulas easier, but it also helps your sheet load faster as it doesn't have to search through the rows to find the correct number!

    The formula knows... ahh, @row, you want this one right here and I don't have to look at anything else.

  • Hi! I had another learning around this I need help with. I realized that if there is no date in the health bubble field then it turns yellow. But I would think if there is no date then it is unplanned so it should be no health bubble. Can someone help show what that would look like?


    FULL FORMULA:

    =IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row = 0, [Start Date]@row < TODAY()), "Yellow", IF(OR([Start Date]@row = "", AND([Start Date]@row > TODAY(), [% Complete]@row = 0)), "", IF(OR([End Date]@row = "", [End Date]@row > TODAY()), "Green", IF([End Date]@row < TODAY(), "Red")))))



  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Lauren,

    No problem! We can just add in a statement that looks at the Start Date and if it's blank, put blank. We'll have this as one of the first rules.


    For this, I'm using "" to indicate is blank:

    IF([Start Date]@row = "", "",


    Now let's add that to the beginning so that it's one of the first thing the formula checks... and if the Start Date is not blank, then it will move on to the next statements:


    =IF([% Complete]@row = 1, "Blue", IF([Start Date]@row = "", "", IF(AND([% Complete]@row = 0, [Start Date]@row < TODAY()), "Yellow", IF(OR([Start Date]@row = "", AND([Start Date]@row > TODAY(), [% Complete]@row = 0)), "", IF(OR([End Date]@row = "", [End Date]@row > TODAY()), "Green", IF([End Date]@row < TODAY(), "Red"))))))


    Cheers!

    Genevieve

  • Gyle Michael Genoso
    edited 10/05/20

    Hi Genevieve,

    This last formula works well, thank you!

    To refine this further, how can I add this rule?

    Red --> [% Complete = 0] AND past the END Date


    So in the screenshot above, I want the "test" task to have a RED health already....

    Here's my sheet formula:

    =IF([% Progress]@row = 1, "Blue", IF(AND([% Progress]@row = 0, [Start Date]@row < TODAY()), "Yellow", IF(OR([Start Date]@row = "", AND([Start Date]@row > TODAY(), [% Progress]@row = 0)), "", IF(OR([Finish Date]@row = "", [Finish Date]@row >= TODAY()), "Green", IF([Finish Date]@row < TODAY(), "Red")))))


    Regards,

    Gyle

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Gyle Michael Genoso

    No problem, I'm happy to help with this!

    The reason you see a Yellow ball when the % Progress is 0 is because of the second statement:

    IF(AND([% Progress]@row = 0, [Start Date]@row < TODAY()), "Yellow",


    There's no criteria here for the Finish Date, so if the row matches these two criteria, the formula will stop at this statement instead of reading on until the final statement. To address this, we just need to add in the criteria that it only turns yellow if the finish date is NOT in the past (or is in the future), as well.


    Try this:

    =IF([% Progress]@row = 1, "Blue", IF(AND([% Progress]@row = 0, [Start Date]@row < TODAY(), [Finish Date]@row >=TODAY()), "Yellow", IF(OR([Start Date]@row = "", AND([Start Date]@row > TODAY(), [% Progress]@row = 0)), "", IF(OR([Finish Date]@row = "", [Finish Date]@row >= TODAY()), "Green", IF([Finish Date]@row < TODAY(), "Red")))))


    Let me know if this works how you'd like!

    Cheers,

    Genevieve

  • @Genevieve P You are amazing! Thank you so much, it works perfectly on my schedule plans! :)

  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful! I'm glad to hear that, and happy to help. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!