Conditional Formatting: Highlight a date if it's been 4 years or more

Dais Helper

New Member
Joined
Jun 11, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I have a column full of various dates -- so far the oldest one is from 2017. I am allowed to get rid of these papers after four years. So 2021 in this case but not 1/1/21, whatever the exact date is or later I would be able to safely get rid of the paper.

So I want to highlight the cells as each one expires as they each reach their individual deadline in 4 years from the date listed in the column.

I found this formula in the forum and thought it would work, but it did not.
=DATEDIF(CD2,TODAY(),"Y")>=4

It highlighted a date clearly within 4 years -- like 2/6/20 & 10/14/19 --- it left some clear as it was supposed to then these chunks of column are highlighted. And it highlighted blank cells.

Is there a formula for conditional highlighting that I can use to highlight only if the date has reached 4 or more years; also do not highlight blank cells?

Is there a Formula that can do this. I really am not comfortable with VBA. It's not imperative that this occurs, so I can do without if VBA is needed.

Thank you very much for your time and assistance. ?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Select CD2 to end of data & use this formula in conditional formatting
=AND(CD2<>"",EDATE(CD2,48)<TODAY())
 
Upvote 0
Select CD2 to end of data & use this formula in conditional formatting
=AND(CD2<>"",EDATE(CD2,48)<TODAY())
thank you -- I wasn't able to get your formula to work correctly. When I tested some dates -- it looked fine until I entered a date I KNEW to be 4 years or beyond and instead of highlighting THAT date it highlighted the 2020 date above it. o_O But I WAS able to get Toadstool's formula to work :)
 
Upvote 0
I entered a date I KNEW to be 4 years or beyond and instead of highlighting THAT date it highlighted the 2020 date above it
That sounds as though the applies to range was from CD1 not CD2. But glad you've got a solution & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,215,039
Messages
6,122,802
Members
449,095
Latest member
m_smith_solihull

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top