Am stuck with an IF(AND formula

KarineMul22_TPg

=IF(AND([% Complete]@row < 1, [End Date]@row > TODAY(), "Red", IF(AND([% Complete]@row < 1, [End Date]@row <= TODAY(), "Amber", "Green"))))

which should mean (in my dreams :))

if under 100% and the date is past, then red

if under 100% and the date is today and future, then amber

otherwise green

Where am I wrong please

Jeff Reisman

@KarineMul22_TPg

I think you're close, just have some misplaced parentheses and incorrect operators. Let's see if this works:

=IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(AND([% Complete]@row < 1, [End Date]@row >= TODAY()), "Amber", "Green"))

KarineMul22_TPg

I also tried

=IF(AND([End Date]@row >= TODAY(), [% Complete]@row = 1, "Green", IF(AND([End Date]@row >= TODAY(), [% Complete]@row > 0, "Amber", "Red"))))

Jeff Reisman

@KarineMul22_TPg

I think you're close, just have some misplaced parentheses and incorrect operators. Let's see if this works:

=IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(AND([% Complete]@row < 1, [End Date]@row >= TODAY()), "Amber", "Green"))

KarineMul22_TPg

Thank you, that works and makes perfect sense - now I have a new problem! as I want my Green, Amber, Red to show as the coloured dots equivalent but some do and some still show the Name of colour instead, whereas the column property is restricted to symbols

Any idea please?

KarineMul22_TPg

Sorry, I 've found out! It's because I use "Amber" instead of "Yellow", doh!