Health Color Bubbles Tied to % Completion & Dates

Lauren Smith

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

Genevieve P.

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

Lauren Smith

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.

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

Lauren Smith

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.

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.

Lauren Smith

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.

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

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.

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

Gyle Michael Genoso

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

Genevieve P.

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