# 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!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### nardagus

##### Active Member
Hello,

Well. You need to create two rules. One rule for each of your points.

As for conditions:

This \$E1 will tell Excel to check proper cell of a row.
Formula says: If E1 is empty put 0, otherwise subtract today's E1 from todays date. If result is 30 or above fire formatting. Otherwise skip it.

#### Fluff

##### MrExcel MVP, Moderator
Another option
Excel Formula:
``````=AND(E2<>"",E2<=EDATE(TODAY(),-1)
=E2=TODAY()``````

#### David77

##### Board Regular
Another option
Excel Formula:
``````=AND(E2<>"",E2<=EDATE(TODAY(),-1)
=E2=TODAY()``````
Thank you so much Fluff!!

May I ask, if I wanted to do this formula:

=AND(E2<>"""";E2<=EDATE(TODAY();-1)) (the first one you made).

But with +6 months.

How would I do that? I tried doing it by just replacing -1 with +6, but it doesn't accep that.

Can you help me out?

Thank you!!!

#### Fluff

##### MrExcel MVP, Moderator

You have too many "" in the first part of the formula, otherwise it should work.

#### David77

##### Board Regular
Fina
Another option
Excel Formula:
``````=AND(E2<>"",E2<=EDATE(TODAY(),-1)
=E2=TODAY()``````

Final note!

Is it possible to update the formatting, so if the cell is blank, it won't be highlighted either? Otherwise it looks like this:

#### David77

##### Board Regular

I
You have too many "" in the first part of the formula, otherwise it should work.
I tried editing it now:

It should highlight cell K2, but it doesnt Not sure why

#### Fluff

##### MrExcel MVP, Moderator
It should be Edate not Kdate

#### David77

##### Board Regular
He
It should be Edate not Kdate
Hello Fluff,

I wrote Edate now:

But it still doesnt work, I tried entering an old date (15-10-2015) and it does not get highlighted

Any tips?

#### David77

##### Board Regular
It should be Edate not Kdate
Nevermind! I should have entered 2025.

It works now - thank you so much Fluff!!! You're amazing :D :D

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