Compare Date Columns And Highlight Differences

[DataWrangler]

Excel 2007/Windows 7 Pro.

Thanks in advance. I have dates arranged in columns and I would like to compare column B to column A, column C to column B, column D to column C, and so on up to column n compared to column (n-1). See example table below.

Easy enough to compare with, for example, =if(B2=A2, "SAME", "DIFFERENT").

But I am looking for a way to do the comparison without inserting a column between A and B, between B and C, between C and D, etc. I've experimented with various combinations of nested IFs and VLOOKUPs in combination with conditional formatting, but I just can't make it work.

Desired end state is that any date that doesn't match the date in the column to which it's being compared is highlighted/formatted as a change.

So in the below, in row 1, all dates are 5/8/17, no changes.
In row 2, all the same up to column D. Format column D as a changed date.
In row 3, all the same up to column C. Format column C as a changed date. blank<blank> is a valid value in the dataset.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Original
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]5/8/17
[/TD]
[TD]5/8/17
[/TD]
[TD]5/8/17
[/TD]
[TD]5/8/17
[/TD]
[/TR]
[TR]
[TD]6/3/17
[/TD]
[TD]6/3/17
[/TD]
[TD]6/3/17
[/TD]
[TD]7/1/17
[/TD]
[/TR]
[TR]
[TD]<blank>blank</blank>
[/TD]
[TD]<blank>blank</blank>
[/TD]
[TD]11/23/17
[/TD]
[TD]11/23/17
[/TD]
[/TR]
[TR]
[TD]6/1/17
[/TD]
[TD]6/1/17
[/TD]
[TD]6/1/17
[/TD]
[TD]<blank>blank</blank>
[/TD]
[/TR]
[TR]
[TD]10/3/17
[/TD]
[TD]10/5/17
[/TD]
[TD]10/5/17
[/TD]
[TD]10/5/17
[/TD]
[/TR]
[TR]
[TD]12/11/16
[/TD]
[TD]12/11/16
[/TD]
[TD]11/30/17
[/TD]
[TD]11/17/17
[/TD]
[/TR]
[TR]
[TD]7/4/1776
[/TD]
[TD]7/20/1969
[/TD]
[TD]12/7/1941
[/TD]
[TD]10/21/2015
[/TD]
[/TR]
</tbody>[/TABLE]
</blank>

 

[Scott T]

try

Excel 2010[TABLE="class: grid, width: 400"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[TH]D
[/TH]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Original
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"]5/8/2017
[/TD]
[TD="align: right"]5/8/2017
[/TD]
[TD="align: right"]5/8/2017
[/TD]
[TD="align: right"]5/8/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"]6/3/2017
[/TD]
[TD="align: right"]6/3/2017
[/TD]
[TD="align: right"]6/3/2017
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]7/1/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]blank
[/TD]
[TD]blank
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]11/23/2017
[/TD]
[TD="align: right"]11/23/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: right"]6/1/2017
[/TD]
[TD="align: right"]6/1/2017
[/TD]
[TD="align: right"]6/1/2017
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]blank
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: right"]10/3/2017
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]10/5/2017
[/TD]
[TD="align: right"]10/5/2017
[/TD]
[TD="align: right"]10/5/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: right"]12/11/2016
[/TD]
[TD="align: right"]12/11/2016
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]11/30/2017
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]11/17/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: right"]7/4/1976
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]7/20/1969
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]12/7/1941
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]10/21/2015
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet5​

Select B2:D8 in conditional formatting use

=A2<>B2

select your formatting

 

[DataWrangler]

@ScottT, thanks! I knew I was overengineering it.

Easy and elegant.

 

[Scott T]

You're welcome