Conditional Formatting regarding expiry dates

Question

Conditional Formatting regarding expiry dates

Nicola Eades 0

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!

Hristo Ivanov 0 Reputation points

2024-04-10T07:27:55.5233333+00:00

Hello,
I also would like to use conditional formatting with a RAG System, where Red is expired date, Amber is due to expire within a month, Green - in date.

Would you be so kind to assist?

Many thanks!

Regards
Barry Schwarz 4,871 Reputation points

2024-04-11T01:36:56.2366667+00:00

Follow the example in Hua's answer.

3 answers

Your answer

Hristo Ivanov 0 Reputation points

2024-04-10T07:27:55.5233333+00:00

Hello,
I also would like to use conditional formatting with a RAG System, where Red is expired date, Amber is due to expire within a month, Green - in date.

Would you be so kind to assist?

Many thanks!

Regards
Barry Schwarz 4,871 Reputation points

2024-04-11T01:36:56.2366667+00:00

Follow the example in Hua's answer.

Answer 1

Karthick G 5

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

User's image

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

User's image

Add new rule as below

User's image

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

User's image

Add new rule as below and select red in format

User's image

Add new rule as below and select orange in format.

User's image

Final conditional formatting rules manager is User's image

Click Ok and you can see the expected result.

User's image

Nicola Keery 0 Reputation points

2025-12-18T11:09:45.9133333+00:00

Exactly what I have been hunting for and worked perfectly - thank you!

Answer 2

Hi @Nicola Eades

Could you please share us with a simple sample?

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

User's image

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)

User's image

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.

Anonymous

2025-11-05T17:16:54.2233333+00:00

How do I take the "Yellow" formula and make it days vs. months?

Answer 3

Nicola Eades 0

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... User's image

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

Jiajing Hua 18,140 Reputation points Moderator

2024-01-23T07:23:22.35+00:00

Hi @Nicola Eades

Thanks for your sharing.

I am confused about the data in those cells, are they text?

Such as the "ESC Card" column and "Engineer (Supervisor)" row, the data is "Expires 31/07/2 4“, if it means this training expires on July 31, 2024, what does the last number 4 mean?

The reason I need to know the meaning of these data is so that I can extract the expiration date for comparison.

Thanks for your understanding.
Nicola Eades 0 Reputation points

2024-01-23T09:41:43.5533333+00:00
Hello @Jiajing Hua

Thanks for your reply

It's a bad screenshot - the '4' has gone down to the next line as the columns are not wide enough!

Let me give you a basic example...

So above is an example

I would like to: -

Highlight dates where accreditations have expired (red)

Highlight dates where accreditations are due to expire within 3 months (orange)

Highlight dates where accreditations have at least 6 months left (green)

I hope that makes it easier to understand what I am after? :)

If you have any more questions, please ask!

Thank you

Nic
Jiajing Hua 18,140 Reputation points Moderator

2024-01-24T07:48:22.7333333+00:00

Hi,

Please check whether the formula is helpful.

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

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

Green: =IF(EDATE(TODAY(),6)<B2,1,0)

If the expiration date is greater than 3 months but within 6 months, no conditional formatting will be set.
Jiajing Hua 18,140 Reputation points Moderator

2024-01-26T08:24:50.62+00:00

Hi， I am checking this thread, if you have a chance to be back, please check whether my information is helpful. Any issues, welcome to discuss with us.

Share via

Conditional Formatting regarding expiry dates

3 answers

Your answer