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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Some videos you may like

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

1605703495028.png


with your formula

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
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
If you mean if K is greater than M +6 months, then change Today() to M2
 

David77

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

ADVERTISEMENT

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

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

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