asked on

Conditional formatting on dates

Hi,

I want to use conditional formatting in an excel document to highlight:

Out of date dates - red
Will expire next calendar month (or in 30 days) - orange
In date - green

I have managed the red and green but the orange doesn't seem to work no what order I put the rules in.

I currently have my dates in format dd-yyyy as a specific date is not really relevant to my data

Any help greatly appreciated!

Many Thanks

SOLUTION

Koen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Automotion

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leigh Macphee

ASKER

Thanks for all your replies.

Koen I have tried to apply your rules but it only seems to be picking up the orange and green values (apologies if I'm just being really dumb).

I have attached a sample file.

To try and explain, I am setting up a database which will contain links to a whole load of documents; these documents will need updating periodically.

I have formula '=DATE(YEAR(C4) + 1, MONTH(C4), DAY(C4))' in column 'I' as the majority of documents need reviewing every year however I am happy to get rid of this and enter all the review dates manually if it will work better! I then want the formatting to turn it red if the date has passed orange if date is in the next 30 days and green if it's still in date.

I am also happy to change column 'I' to dd-mm-yyyy if this will work better.

Many Thanks,

Leigh Macphee

ASKER

Whoops sorry forgot to actually upload file............
SHE-draft.xlsx

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leigh Macphee

ASKER

Rob Henson - spot on! Thank you very much!

Thanks again for all your quick answers everyone - much appreciated :)

Rob Henson

Thanks for the feedback.

BTW the NOW funcion gives current date and time whereas TODAY only gives date.