Conditional formatting expiration date

David77

Board Regular
Hello everybody,

I want to create a conditional formatting for the following column in my stock portfolio:

I would like 2 conditional formatting (i have two of these sheets, which are identical, so one for each):

1) I would like the first formula to highlight the cells if the date entered is 1 month ago or more from the current date. I imagine some sort of =today()-30, but I tried this and it did not work Would appreciate any help!
2) Same as 1) but when the data in the cell is = to the current data. Also highlighted with red.

Thank you everybody!

Fluff

MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

David77

Board Regular
You're welcome & thanks for the feedback.
Hello Fluff,

I am terribly sorry for disturbing you again. I promise this is the last inquiry in this thread.

It seems that I misunderstood the conditional formatting and it does, after all, unfortunately not work

I currently have this setup:

So I want the date in the column to be highlighted with a red color once it has passed the date stated in the column (So NOT todays date, but the day stated in the column) and an additional 6 months. So basically: Date stated in column + 6 months = highlight the cell with a red color.

Is this possible?

Fluff

MrExcel MVP, Moderator
If you mean if K is greater than M +6 months, then change Today() to M2

David77

Board Regular
If you mean if K is greater than M +6 months, then change Today() to M2
Sorry if I was incoherent in my question. What I mean is if K is greater than K + 6 months, then highlight the K cell with a red color.

So for example cell K80 has the date 05-10-2020 (5th of October, 2020). So the formula should highlight this cell with a red filling once we arrive at K80 + 6 months = the 5th of of April, 2021 (05-04-2020).

Does this make sense? And is it possible?

Thank you for your fast response Fluff!

Fluff

MrExcel MVP, Moderator

In that case use
Excel Formula:
``K2<=EDATE(TODAY(),-6)``

David77

Board Regular
In that case use
Excel Formula:
``K2<=EDATE(TODAY(),-6)``

It works!!!!!

You are a God amongst men in this Excel forum Fluff Thank you so incredibly much! I literally cannot express my gratitude enough !

Fluff

MrExcel MVP, Moderator
Glad to help & thanks for the feedback.

Replies
15
Views
196
Replies
12
Views
557
Replies
1
Views
148
Replies
1
Views
96
Replies
22
Views
518