✭✭

=IF([Actual % Complete]@row) = 100, ""Blue"", IF(ISBLANK([End Date]@row)), ""NS"", IF(TODAY() - ([End Date]@row) > 30), ""Red"", IF(TODAY() - [End Date]@row) <=30), ""Yellow"", IF(TODAY() - [End Date]@row) = 0, ""Green"")))))

Tags:

• ✭✭✭

Gwynneth,

Is there a specific reason for the double "" "" around text?

Try this:

=IF([Actual % Complete]@row) = 100, "Blue", IF(ISBLANK([End Date]@row)), "NS", IF(TODAY() - ([End Date]@row) > 30), "Red", IF(TODAY() - [End Date]@row) <=30), "Yellow", IF(TODAY() - [End Date]@row) = 0, "Green")))))

To make the formula a little simpler, instead of looking for anything that is less than or equal to 30 away, look for anything that equals 0. This way you can just say "If the project is not over 30 days late and not on time, then show me a yellow status".

=IF([Actual % Complete]@row) = 100, "Blue", IF(ISBLANK([End Date]@row)), "NS", IF(TODAY() - ([End Date]@row) > 30), "Red", IF(TODAY() - [End Date]@row) = 0, "Green", "Yellow")))))

Let me know how it goes!

Lidiya Shutaya

lidiya@ddbconsultants.ca

• ✭✭

### #UNPARSEABLE

#### Cause

The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

#### Resolution

Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").

• ✭✭✭

Sorry that did not work!

Took another look through the formula, let's see if it's a parentheses issue, try this:

=IF([Actual % Complete]@row = 100, "Blue", IF(ISBLANK([End Date]@row), "NS", IF((TODAY() - ([End Date]@row) > 30), "Red", IF((TODAY() - [End Date]@row) = 0, "Green", "Yellow"))))

If your Actual % Complete is a percentage, then use =1 instead of =100 because the formula will look at it as a decimal not a percentage. ie 90% = .9

Best,

Lidiya Shutaya

lidiya@ddbconsultants.ca

• ✭✭

Hi Lydia,

Thanks for your help. I did that, it still didn't work. I also used your copy above with the one set of quotations.

Gwynneth

• ✭✭

However, copying and pasting your formula over mine did, work! Go figure. Thanks for your help.

• ✭✭✭

• ✭✭✭

Gwynneth,

Is there a specific reason for the double "" "" around text?

Try this:

=IF([Actual % Complete]@row) = 100, "Blue", IF(ISBLANK([End Date]@row)), "NS", IF(TODAY() - ([End Date]@row) > 30), "Red", IF(TODAY() - [End Date]@row) <=30), "Yellow", IF(TODAY() - [End Date]@row) = 0, "Green")))))

To make the formula a little simpler, instead of looking for anything that is less than or equal to 30 away, look for anything that equals 0. This way you can just say "If the project is not over 30 days late and not on time, then show me a yellow status".

=IF([Actual % Complete]@row) = 100, "Blue", IF(ISBLANK([End Date]@row)), "NS", IF(TODAY() - ([End Date]@row) > 30), "Red", IF(TODAY() - [End Date]@row) = 0, "Green", "Yellow")))))

Let me know how it goes!

Lidiya Shutaya

lidiya@ddbconsultants.ca

• ✭✭

### #UNPARSEABLE

#### Cause

The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

#### Resolution

Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").

• ✭✭✭

Sorry that did not work!

Took another look through the formula, let's see if it's a parentheses issue, try this:

=IF([Actual % Complete]@row = 100, "Blue", IF(ISBLANK([End Date]@row), "NS", IF((TODAY() - ([End Date]@row) > 30), "Red", IF((TODAY() - [End Date]@row) = 0, "Green", "Yellow"))))

If your Actual % Complete is a percentage, then use =1 instead of =100 because the formula will look at it as a decimal not a percentage. ie 90% = .9

Best,

Lidiya Shutaya

lidiya@ddbconsultants.ca

• ✭✭

Hi Lydia,

Thanks for your help. I did that, it still didn't work. I also used your copy above with the one set of quotations.

Gwynneth

• ✭✭