IF AND Formulas - Where am I going wrong

Vikki Cooper

Morning All,

I have been trying to work out a formula for a calculation, i am not very good at formulas so i cant see where i have gone wrong, can any one help please?

I need the below:

If today > actual start date & today is > actual end date & % complete < 100% = Red

If today > actual start date & today is < actual end date & % complete > 0% = Green

If today < actual start date & today is < actual end date & % complete <= 100% = Green

I have the below formula

=IF(AND(TODAY() > [Actual Start Date]64, TODAY() > [Actual End Date]64, <0.99 [%Complete]64), "Red", IF(AND(TODAY() > [Actual Start Date]64, Today() < [Actual End Date]64, <= 0 [%Complete]64), "Green", IF(AND(TODAY() < [Actual Start Date]64, TODAY() < [Actual End Date]64, <=0.1 [% Complete]64), "Green")

Any help would be greatly appreciated

MattH

The following formula definitely provides the results expected to the three above conditions:

=IF(AND(TODAY() < Start1), "Green", IF(AND(TODAY() > Start1, TODAY() < End1, NOT(Complete1 = 0)), "Green", IF(AND(TODAY() > Start1, TODAY() > End1, Complete1 < 1), "Red", "Red")))

 

I didn't put much thought into whether or not OTHER conditions would be a problem, but I wanted to give you these thoughts: 

1. In the final condition, basically for things in the future, I only look at whether or not the start is in the future. If it is, the end date cannot possibly be before the start, and the % complete is irrelevant because no one is expected to start it yet. (And, if they have, either way, it would be Green). 

2. See the screen shot for the sheet I made. Anytime there are nested IFs I find it to be much easier to create the separate conditions like you did, "in English", then make those formulas one at a time. This ensures that the formula is correct before combining them into one. If you have problems with combining them, then you know it's with parenthesis and not the "original" formulas.