CODEWITHSUNDEEP
excelconditional-formatting
user6641597
user6641597

Reputation: 37

Why is this condition comparing dates returning true?

I have a column full Dates and I am trying to highlight all of the dates if they are before 30 before todays date.

An example of why what I have that is not working is the value in the cell is 4/12/2017 and it is being highlighted when only values before 4/01/2017 should be highlighted (today is 5/01/2017).

My rule in conditional formatting is 

Format only cells with:
cell value     less than     =Today()-30
  *change background color*

but the 04/12/2017 is still being highlighted.

Upvotes: 0

Views: 38

Answers (1)

Krypton
Krypton

Reputation: 5286

Select the first cell in the range you need to format, then put that cell into the formula. For example, if you select A1 change the formula to:

=A1<TODAY()-30

Then click OK and check your 'applies to' range.

You probably also want to filter out blank cells. For this you can change the formula to:

=AND(A1<TODAY()-30,A1<>"")

Upvotes: 1

Related Questions