Conditional formatting with dates in Google Sheets

Question

I'm trying to add a little functionality in my register to help me catch incorrectly entered dates. I've got a column, A, for dates where no date entered should be earlier than the previously entered date. Normally I'd use if "Date is Before" the "Past Week" or so, but I'm not necessarily going to be entering dates every week or every two weeks.

So my desired functionality is "highlight red if date is before date entered in the cell above." I'd also like to add "highlight orange if date is equal to date entered in the cell above." How do I accomplish this? I'm aware of the DATEIF and ISDATE functions, but the documentation is a little confusing to me and I don't entirely understand how to use them.

Answer 1

Use this conditional formatting custom formula rule to highlight dates in column B3:B that are before the date in the cell above:

=( (B3 < B2) + not(isdate(B3) * isdate(B2)) ) * len(B3) * len(B2)

...and this to highlight dates in column B3:B that equal the date in the cell above:

=( (B3 = B2) + not(isdate(B3) * isdate(B2)) ) * len(B3) * len(B2)