Conditional formatting expiration date

[David77]

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!

 

[nardagus]

Hello,

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

As for conditions:

AD1. I used this: =IF($E1="";0;Today()-$E1)>=30
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.

AD2. =$E1=Today()

 

[Fluff]

Another option

=AND(E2<>"",E2<=EDATE(TODAY(),-1)
=E2=TODAY()

 

[David77]

Another option

=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]

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

 

[David77]

Fina

Another option

=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]

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]

It should be Edate not Kdate

 

[David77]

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]

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

Have a great day! Marked your response as the correct answer