Nested IF for symbols health status

claude.ngongang80316

Good Day!

I am having an error message #UNPARSABLE. I am trying to create a formula to automatically change the health status project tasks. Our project management life cycle is very simple, we track only 6 milestones. Thus, I would like to compare all 6 milestones and provide the overall health status R-G-Y according to the rule below:

If the milestone delivery date is within the 10 coming days, the overall health is ''Red''

If the milestone delivery date is within the 11 and 20 days, the overall health status is ''Yellow''

If the milestone delivery date is greater that 21 days, the overall project status is ''Green''

IF(([Date Milestone1]@row<=[Date Milestone1]@row+10,"Rouge"; IF([Date Milestone1]@row>([Date Milestone1]+20);"Vert"; "Jaune"))......

Since I have 6 milestones, I think i need to add OR in the formula. Am I on the right track?

Date M1 or Date M2 or Date M3 or Date M4 or date M5 or Date M6 within 10 days; ''Red''

Date M1 or Date M2 or Date M3 or Date M4 or date M5 or Date M6 within greater than 20 days; ''Green''; ''Yellow''

Thanks,

Claude

Genevieve P.

Hi Claude,

The first thing to adjust will be what you're looking for... right now you're asking your statement to see if the date in the Milestone 1 column is that date, +10 (which it never will be). Instead, we'll want to use the TODAY formula to look to see if that date is within the next 10 days from Today: TODAY(10)

=IF([Date M1]@row <= TODAY(10); "Rouge"; IF([Date M1]@row < TODAY(20); "Jaune"; "Vert"))

You'll notice I also swapped around your rules to make sure that the Yellow would appear if the date is less than 20 days away (but later than 10 days away, which the Red rule indicates).

Then in regards to all 6 of your dates, we can definitely add in an OR statement to look in each cell. However, this will return either Red or Yellow if only one of the 6 meets that criteria, even if all the other milestones are green - is that what you want?

To add the OR, you would do it after each IF and state each column and the outcome you're looking for. Here's the RED rule, for example:

IF(OR([Date M1]@row <= TODAY(10); [Date M2]@row <= TODAY(10); [Date M3]@row <= TODAY(10); [Date M4]@row <= TODAY(10); [Date M5]@row <= TODAY(10); [Date M6]@row <= TODAY(10)); "Rouge"

Then we do the same for the yellow rule. Keep in mind that you'll have to replace the [Date] Column names with the actual names from your sheet.

Here's the full formula:

=IF(OR([Date M1]@row <= TODAY(10); [Date M2]@row <= TODAY(10); [Date M3]@row <= TODAY(10); [Date M4]@row <= TODAY(10); [Date M5]@row <= TODAY(10); [Date M6]@row <= TODAY(10)); "Rouge"; IF(OR([Date M1]@row < TODAY(20); [Date M2]@row < TODAY(20); [Date M3]@row < TODAY(20); [Date M4]@row < TODAY(20); [Date M5]@row < TODAY(20); [Date M6]@row < TODAY(20)); "Jaune"; "Vert"))

Here are some Help Center articles I used: TODAY Function / IF Statement / OR Function

Let me know if this works, or if I can clarify anything further for you!

Cheers,

Genevieve

Genevieve P.

Hi Claude,

The first thing to adjust will be what you're looking for... right now you're asking your statement to see if the date in the Milestone 1 column is that date, +10 (which it never will be). Instead, we'll want to use the TODAY formula to look to see if that date is within the next 10 days from Today: TODAY(10)

=IF([Date M1]@row <= TODAY(10); "Rouge"; IF([Date M1]@row < TODAY(20); "Jaune"; "Vert"))

You'll notice I also swapped around your rules to make sure that the Yellow would appear if the date is less than 20 days away (but later than 10 days away, which the Red rule indicates).

Then in regards to all 6 of your dates, we can definitely add in an OR statement to look in each cell. However, this will return either Red or Yellow if only one of the 6 meets that criteria, even if all the other milestones are green - is that what you want?

To add the OR, you would do it after each IF and state each column and the outcome you're looking for. Here's the RED rule, for example:

IF(OR([Date M1]@row <= TODAY(10); [Date M2]@row <= TODAY(10); [Date M3]@row <= TODAY(10); [Date M4]@row <= TODAY(10); [Date M5]@row <= TODAY(10); [Date M6]@row <= TODAY(10)); "Rouge"

Then we do the same for the yellow rule. Keep in mind that you'll have to replace the [Date] Column names with the actual names from your sheet.

Here's the full formula:

=IF(OR([Date M1]@row <= TODAY(10); [Date M2]@row <= TODAY(10); [Date M3]@row <= TODAY(10); [Date M4]@row <= TODAY(10); [Date M5]@row <= TODAY(10); [Date M6]@row <= TODAY(10)); "Rouge"; IF(OR([Date M1]@row < TODAY(20); [Date M2]@row < TODAY(20); [Date M3]@row < TODAY(20); [Date M4]@row < TODAY(20); [Date M5]@row < TODAY(20); [Date M6]@row < TODAY(20)); "Jaune"; "Vert"))

Here are some Help Center articles I used: TODAY Function / IF Statement / OR Function

Let me know if this works, or if I can clarify anything further for you!

Cheers,

Genevieve