Conditional Formatting Problem

[Stov]

Hi everyone,

I'm designing a spreadsheet which enables me to check my work authorisations and I'd like it to do a number of things.

In column D I have the date I was authorised for the task, and then in column E, the date I am expired - I have been able to work this out!

My problem is that I'd like the dates in column E with conditional formatting to display Green if there is still over a month until this date is to expire, Amber if a month is left to go and finally red if this date has expired so I need to do something about it. So for example:

If the date is today, it should be Red, if the date is from the 29th August until yesterday I want it to display amber, and finally green if the date is over a month away.

So in E27 I have the column calculating my date in D27 + a year. (D27 shows 27 Mar 2011, E27 shows 27 Mar 2012.)

With conditional formatting;

To show red on the date, I have:
Cell Value Is | Greater than or equal to | =Today()

To show amber I have:
Cell Value is | Less than | =Today()

and whatever I try for green it doesn't work, although I suspect this is to do with the amber entry?

Hopefully that has made sense, any help is massively appreciated!

 

[Stov]

I've just noticed I've had the 2 colours the wrong way around:

Red is:
Cell Value Is | Less than or equal to | =Today()

and Amber is:
Cell Value Is | Greater than | =Today()
How do you distinguish this to check between 1 and 30?

It's just the green... I've tried Today()+31...

 

[RomulusMilea]

Hi Stov and Welcome on board !

Here is the solution I propose to your problem. Please set up conditional formatting in the following order:

1). Red condition formula: =IF(E27="",FALSE(),TODAY()>=E27)

2). Amber condition formula: =IF(E27="",FALSE(),AND(TODAY()>=DATE(YEAR(E27),MONTH(E27)-1,DAY(E27)),TODAY()<E27))< p>

3). Green condition formula: =IF(E27="",FALSE(),TODAY()<DATE(YEAR(E27),MONTH(E27)-1,DAY(E27)))< font>.

Please confirm the solution meets your expectation.

 

[RomulusMilea]

Hi again,

For some reasons, 3rd condition was not displayed correctly (I tried to edit the message, it did not work), here is the correct one:

=IF(E27="",FALSE(),TODAY() < DATE(YEAR(E27),MONTH(E27)-1,DAY(E27)))

Please confirm the solution meets your expectation.

 

[Stov]

Hi RomulusMilea,

Thank you so much for your help!

I'd played around with the date formulas and just couldn't work it out.

Your solutions were perfect save for 2 single brackets on the amber code at the end.

Thanks again!