Conditional Formatting Dates

[Syed1961]

Sample File attached...

There is expiry dates which I want to highlight as:

if today's date fall between 15 May to 31 May
then, expiry dates between 01 Jun to 15 June should be highlighted YELLOW
AND expiry dated between 15 May to 31 May should be red

similarly
if today's date falls between 01 jun 16 to 15 june 16
then, expiry dates between 16 Jun 16 to 30 June - should be YELLOW
AND expiry dated between 01 Jun to 15 June should be red

I think solution to this requirement is very simple, but somehow, I am not getting an idea to achieve this.

I am trying to achieve this by using conditional formating.
Need your ideas - or solution for my above requirement.

Appreciate your support. And thank you in advance fo

 

[Chihiro]

Well, it's not as simple as you may think.

What should happen if today is 01 Jun 2016 and Expiry is 24 May 2016?

What about February? Should it be like below?
If today's date is between 01 Feb 2017 & 15 Feb 2017
Then, expiry dated between 16 Feb 2017 & 28 Feb 2017 Yellow
And expiry dated between 01 Feb 2017 & 15 Feb 2017 Red

 

[Syed1961]

Well, it's not as simple as you may think.

What should happen if today is 01 Jun 2016 and Expiry is 24 May 2016?

What about February? Should it be like below?
If today's date is between 01 Feb 2017 & 15 Feb 2017
Then, expiry dated between 16 Feb 2017 & 28 Feb 2017 Yellow
And expiry dated between 01 Feb 2017 & 15 Feb 2017 Red

Hi Chihiro, thanks for swift response.
on 01 Jun 2016, expiry 24 May 2016 should be RED.
And for February, it should be as you have mentioned.

 

[Chihiro]

I'm sure there's more efficient formula. But this will work.

Both CF applied to C2:C406 range.

1st CF. (Red Fill)
=AND(YEAR(TODAY())=YEAR($C2),OR(AND(DAY(TODAY())>=15,MONTH(TODAY())=MONTH($C2)),TODAY()>=$C2))

2nd CF (Yellow Fill)
=AND(YEAR(TODAY())=YEAR($C2),OR(AND(MONTH(TODAY())=MONTH($C2),DAY(TODAY())<16,DAY($C2)>15),AND(MONTH(TODAY())+1=MONTH($C2),DAY(TODAY())>15,DAY($C2)<16)))

NOTE: I purposefully left AND(YEAR(TODAY())=YEAR($C2),) part so that anything older than a year for 1st CF won't turn red (assuming it would have been addressed by then). If that's not the case adjust 1st CF to...

=OR(AND(DAY(TODAY())>=15,MONTH(TODAY())=MONTH($C2),YEAR(TODAY())=YEAR($C2)),TODAY()>=$C2)

See attached as well (Column D has above formula to demonstrate how it works).