Conditional format color is set correct, yet color will not change

[Mark]

For Red:

Condition1:
Formula Is =AND(ISNUMBER(A1),A1>=14)

For Green:
Condition2
Formula Is =AND(ISNUMBER(A1),A1<15)

If I put in 01/01/2009 - 01/21/2009 cell DOES turn red.

If I change the second date(01/21/2009) (20days) to 1/04/2009 ( 3 days) the
cell Stays RED
or if I have a green cell IT STAYS GREEN when the dates are altered.. Is
this a one time shot?..

Any ideas

And I apologise if I have posted this more than once . I am over my head and
have reached out on 3 topics...

Thanks for understanding Mark

 

[Pete_UK]

What does A1 actually contain?

Pete

 

[milo]

Try this

Condition1: (Red)
=AND(ISNUMBER(A1),DAY(A1)<14)


Condition2: (Green)
=AND(ISNUMBER(A1),dar(A1)<15)

Milo

 

[milo]

Sorry!

Condition1: (Red)
=AND(ISNUMBER(A1),DAY(A1)<14)


Condition2: (Green)
=AND(ISNUMBER(A1),dar(A1)<15) <------ sorry mistyped
=AND(ISNUMBER(A1),DAY(A1)<15) <------ this one correct

Milo

 

[milo]

Sorry again!
I hope this should help

Condition1: (For Red)
=AND(ISNUMBER(A1),DAY(A1)<=14)

Condition2: (For Green)
=AND(ISNUMBER(A1),DAY(A1)>=15)

Regards,

 

[Mark]

A1 contains a date 01/01/2009
A2 contains a date 01/22/2009

A3 would be the difference in that date (turns red)

I set conditonal in A3 if equal to or less than 14 turn green
Add....If greather than 14 turn red.

Whichever color comes up 1st in A3 be it a red number or green, the other
conditon does NOT change if I change the date in A2 to )1/03/2009 or (2) it
should turn green but it stays red.. It stays at the 1st color associated
with the 1st date entered in A2 . Even if I F9 or shift F9 with a new date in
A2 the color will not change from whatever came in 1st.

Thanks

A1 = 01/01/2009
A2 = 01/10/2009

 

[Pete_UK]

So why did you quote A1 in the formula that you gave in your first
post?

I suggest you check that those formulae actually refer to A3.

Anyway, you have an overlap on those conditions. The first one (which
takes priority) is looking for values greater than or equal to 14,
whereas the second one is less than 15, so the first criteria will
always apply if the number is 14.

Hope this helps.

Pete

 

[Mark]

Came up with:

you may not use unions, intersections or array constants for conditional
formatting criteria

 

[Mark]

I'm sorry that I didn't write A3, but I see ,you see the problem and I hear
you, that one overides the other.

But if I change the date in A2 and it changes A3 to</>14 Is there no
conditional formula that will work to make it red/green based on </>14?

thanks pete

very much

 

[Mark]

I see what one of the issues are.

If I make a cell C7 and it is =C7-C6 and use just numbers not dates , they
will turn green or red based on 2 conditionals of

equal to or less than 14 shade green

add

Greater than 14 shade red.

THAT works!

But when I use DATES! The sum in my total box C7 does not compute to a real
number. It is looking at it as something else other than the number. Maybe
date days or something . so I guess the answer is to make that difference in
dates a number or some kind of special value?

Any ideas?


Mark

 

[Mark]

And to reclarify,

A1 you spoke of is actually C7 which is the difference in days between dates
entered in C5 and C6

I knew to change the cell number I was using a generic, but nonethe less
once you turn a cell a color it STAYS THAT WAY REGARDLESS OF ANY DATE CHANGE
ABOVE. Yopu can't even get it to go back whit. If it's red it's always red..
Seems like a flaw.

Mark

Confused yet..

 

[Pete_UK]

I can't get back to you at the moment, Mark - football on TV !!

Pete

 

[milo]

Hi,

Seems that you want the conditional occur only on cell A3. What I can see
is:-
A1 contains a date 01/01/2009
A2 contains a date 01/22/2009
A3 contains the different of how many days when you subtract date A1 from A2
" =A2-A1". And this cell must come with conditional format if the day
equal
to less then or equal to 14 should turns red and if it is greater than
or equal to
15 should turns green.

Suggestions:

1) Firstly, Try the following conditional formats and apply them to cell A3:
Conditions 1: (Red)
=AND(ISNUMBER(A3),DAY(A3)<=14)

Conditions 2: (Green)
=AND(ISNUMBER(A3),DAY(A3)>=15)

2) Secondly, make sure font format in cell A3 not formated as "Red" or
"Green" in color.

3) Lastly, make sure you have entered a valid date in cell A1 and A2.
(d/m/yyyy or m/d/yyyy)

4) Ok, finally, probably you should try any of these formula instead and put
it in cell A3.
=ABS(A1-A2)
or
=Datedif(A1,A2,"md")

Regards.