Excel 2007 : Date expiry conditional formatting in Excell 2007 (I think it is the 2007 version anyway)

[[email protected]]

Hey all,

I am struggeling with getting my certificats expiry dates light up 2 years, 1 year; 6 months, ... before expiry and on expiry.
With this in the boxes. "- 2 years" in a green box. "-1 year" in a light green box. "- 6 months" in a yellow box, ...

See attached for my file. I tried with the less then 2 years valid but struggle with this pretty some. Tried so many codes so far in conditional formatting but not working all as should.

Hope anyone can help me out with this?

Thanks so much for the help in advance,

Nico

[npamcpp]

Hi,

create these rules in this order and set them to stop when true.

=IF(ISNUMBER(F6),F6<TODAY(),FALSE) --> Red fill
=IF(ISNUMBER(F6),F6-TODAY()<30,FALSE) --> orange fill
=IF(ISNUMBER(F6),F6-TODAY()<180,false) --> yellow fill
=IF(ISNUMBER(F6),F6-TODAY()<365,FALSE) --> pale green
=IF(ISNUMBER(F6),F6-TODAY()<(365*2),FALSE) --> light green

The best way to create formulas for conditional formatting is to create a cell formula that returns true or false, depending on the condition. Then plug that formula into the conditional formatting dialogue. See the formulas I've used to come up with the suggested CF in the attached file.

regards,

[[email protected]]

Dearest npamcpp,

Is there a way to get it looking like this in attachment please?
No working with the true and false then.
If only the first line will be changed, then I should be able to do all the next lines for sure. :-)

Will try to change your formula's a little bit and see what that will say to start with.

Thanks again super much,

Nico

[Cutter]

Duplicate thread: http://www.excelforum.com/excel-work...on-anyway.html

[arlu1201]

Nico,

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.