Trying to narrow down some data

HeRoseInThree

Board Regular
Joined
Jan 11, 2018
Messages
103
I have a range of dates in column A and I only need to identify the dates within the last 2 years. Is there a way to use conditional formatting to only highlight the dates that are within the last 2 years?

Thanks in advance!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
For a value in cell A1, use a Conditional Formatting formula like this:
Code:
=AND(A1>0,A1>EDATE(TODAY(),-24))

If you may have future dates that you want to exclude, add one more condition:
Code:
=AND(A1>0,A1>EDATE(TODAY(),-24),A1<=TODAY())
 
Upvote 0
You are most welcome!

The key is the EDATE function, which is explained in detail here: https://www.techonthenet.com/excel/formulas/edate.php
Basically, we are just looking for data that are after 24 months prior to the current date.
The other checks are just to make sure that the cell isn't empty, and the date is prior to today.
By putting all the conditions in an AND function, it will only return True if all conditions listed are True.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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