How do I add conditional formatting for a row in my table if the value in Date column is not one day greater than one row above?

[Mr. Snrub]

I have a large Excel table with a Date column. The values in Date column range from 6/1/2003 to today. How do I add conditional formatting that will surround a table row with a huge red border (or a highlight in yellow) if the current value of Date is not exactly one day greater than the previous row's date? This way I can tell if there are either some missing or duplicate rows in the table.

 

[jmacleary]

Hi there. You can do this with conditional formatting:

Book1
	A
1
2	06/01/2003
3	07/01/2003
4	08/01/2003
5	09/01/2003
6	10/01/2003
7	11/01/2003
8	13/01/2003
9	14/01/2003
10	01/03/2020
11	02/03/2020
12	03/03/2020
Sheet1

Assuming your data is as shown, put this conditional format in A3 onwards:=$A2+1<$A3
and set the range it applies to to:=$A$3:$A$5000 (replace 5000 with last row)
.

 

[Fluff]

To highlight duplicate rows as well as missing dates try
=$A2+1<>$A3

 

[jmacleary]

Ah yes, fluff is right (as usual) - I missed the 'or duplicate' bit, so use his formula in the conditional formatting.

 

[Mr. Snrub]

Fantastic! That works great. Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback