Format dates in cells to change colour when expired or within 6 months of expiry

[K8BDN]

I have created a worksheet to track training records and requirements. So far, I have been unable to find a way to format the cells to change colour when training validity dates are expired or within 6 months of expiry;

RED - Expired date
ORANGE - Date within 6 months of expiry date

This expiry date is based on the renewal date required for that individual column. Not all training has the same validity date range, so every training has its own timeframe referenced in row 3.

I have done some basic VB coding to allow the grouping and filter features to be used when the sheet is protected. The workbook is attached with the password 123 to unlock.

Thanks for having a look at this. I really appreciate it.

[kersplash]

Can you mock up what you want the formatting to look like for some of your data?

[K8BDN]

Attachment 627438

Just the expired dates to turn bold red and the within 6 months expiry dates to turn bold orange. Thanks.

[kersplash]

Can you update your sample file rather than using an image?

[K8BDN]

OMG....I so should have done that! Please see the attached file.

Thanks.

[JeteMc]

Hello K88DN and Welcome to Excel Forum.
The following formulas used as conditional formatting rules seem to work.
Red: =AND(Z8<>"",DATE(YEAR(Z8)+VALUE(LEFT(Z$3,1)),MONTH(Z8),DAY(Z8))< TODAY())
Orange: =AND(Z8<>"",DATE(YEAR(Z8)+VALUE(LEFT(Z$3,1)),MONTH(Z8)-6,DAY(Z8))<=TODAY())
Let us know if you have any questions.

[K8BDN]

Hi JeteMc,
Thanks for looking into this, I really appreciate it.
I have had some issues applying these formulas on my end. The orange formula is 'taking over' and making both the red and orange formats orange.

Attached is my mock workbook to show this. I'm not sure what I've done wrong.

I do want this orange and red conditional formatting to cover;
Columns Z - FZ
Rows 8 - 200

Cheers, K8BDN

[JeteMc]

The rule for red needs to precede the rule for orange in the rules manager.
Let us know if you have any questions.

[K8BDN]

It worked! Thank you so much.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.