Conditional formatting; mutiple rules

Hi

Hoping for a bit of help on what I assume is quite simple but I just can't sem to figure out how to implement.

I have a column of dates, and I want to use conditional format according to the date shown.

If date is older than today = red
If date is within 6 months of today = yellow
If date is over 6 months from today = green

Adding multiple conditional formatting rules seems to overwrite the previous rule so I was hoping there is one formula which can capture all of the statements.

Many thanks in advanced .

Hi AliGW

Thank you for your reply, I think I have figured out with oyour suggestion.

Many thanks

Hi

This isn’t quite as resolved as I first thought. I have attached the spreadsheet in question.

All contracts have an initial expiry date but some have an extension expiry date. So I have created an extra column (Column H in the attached) so that all expiry dates, regardless of whether they are initial or extensions, are captured in one column. I would then like this column H to have conditional formatting which formats the following.

• Expired
• Expiring in 6 months
• Expiring between 6 months and 1 year
• Expiring over 1 year

But the rules don’t seem to match what I want. Any advised would be hugely appreciated, maybe the formula I put in column H isn’t quite right?

Many thanks in advance