Conditional formatting expiration date

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
Hello everybody,

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



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! :)
 

Some videos you may like

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
Joined
Apr 23, 2012
Messages
317
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
Another option
Excel Formula:
=AND(E2<>"",E2<=EDATE(TODAY(),-1)
=E2=TODAY()
 

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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:

1605098764728.png
 

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

1605098921946.png


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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
It should be Edate not Kdate
 

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
He
It should be Edate not Kdate
Hello Fluff,

I wrote Edate now:

1605683916136.png


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
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,126
Messages
5,570,332
Members
412,319
Latest member
akshat1231
Top