Health Color Bubbles Tied to % Completion & Dates

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 PGenevieve P admin
    Accepted 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

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

  • 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")))))



  • 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

Sign In or Register to comment.