Conditional Formatting regarding expiry dates

Question

Hello All :) I am working on a training matrix for all our employees in Excel. I would like to use some conditional formatting on all the dates. For example, I would like to highlight expiry dates within the next three months And highlight expired dates in red And maybe dates that have been expired for 6 months in yellow Any advice would be appreciated... Thanks!

Answer 1

Hi @Nicola Eades

Could you please share us with a simple sample?

On my simple sample, I divide date comparison into 4 situations.

You may try following steps:

Select the cells that you need to apply conditional formatting > click Home tab > Conditional Formatting > New Rule > Use a formula to determine which cells to format.

Red: =IF(TODAY()>$B2,1,0)

Green: =IF((AND(TODAY()<=$B2,EDATE(TODAY(),3)>$B2)),1,0)

Yellow: =IF((AND(EDATE(TODAY(),3)<=$B2,EDATE(TODAY(),6)>$B2)),1,0)

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

Hi! Thank you So here is an example - obviously the word "expires" is going to be removed - but this is just an example so you can see what I'm trying to achieve...

Hope that helps explain things a bit better...! Any questions, just ask - and thanks so much for your time so far...!

Answer 3

This can be achieved by using helper cells. First you have to remove the Expires from the range. I have used 3 helper cells K1, L1 and M1. where K1 formula is =TODAY() L1 formula is =EDATE(K1,6)

M1 formula is =EDATE(K1,3) and the sample data used is from A2:G11 please refer the image below

Apply below conditional formatting in sequence for the range B2:G11 as below

Add new rule as below and click ok and select stop if true

Add new rule as below

Add new rule as below and select the green in format.

Add new rule as below and select red in format

Add new rule as below and select orange in format.

Final conditional formatting rules manager is

Click Ok and you can see the expected result.