Comparing a list of dates to check they match

Hello
If I understand you correctly, you want to conditionally format a column of dates that don't match your list of 4 dates and also ignore any blank cells in the column. If so try the following conditonal formatting formula:

=AND(A2<>"",ISNA(MATCH(A2,$F$2:$F$5,0)))

Here your column of dates would begin in A2 to A? and your list of 4 dates is situated in cells F2:F5.

Hope this is what you're looking for.
DBY

DBY,

Thanks for the quick reply. No not quite. There is no second list and I'm not looking up against anything else apart from the one list of 4 dates.

benishiryo,

Again, thanks for teh quick reply. A mismatch is defined as any type of mismatch not including blanks. If any one date differs than I'd like it highlighted via conditional formatting. If 1 cell is blank and 3 have the same date then that's fine. If one date is different in the 3 cells then that's a mismatch. In theoy all dates should be exactly the same unless there is no date.

If there is a mismatch we need to update our client's systems so they do match.

Basically if all 4 dates minus blanks match then that is correct. All other scenarios, ignoring blanks, is a mismatch.

Hi
The following conditional formula will highlight a date if it does not match the other 3. But this assumes only 1 date out of the 4 is incorrect. It ignores blanks.

=AND(A1<>"",COUNTIF($A$1:$A$4,A1)=1)

Here your list would be in A1:A4.

DBY

No worries, thanks. Looks like I'll be writing a long IF statement to do what I need. :-)

Hi

Sorry it's not working for you. I was working with what you said here:

If any one date differs than I'd like it highlighted via conditional formatting.

The conditional formula I gave does highlight any one date that differs.

What happens if it's a 50/50 split of the 4 dates, 2 of each. What gets highlighted?

Apoligses, should have said I'd like the header to be highlighted, so A1 would be the header and A2:A5 would be the dates. I can't highlight the individual cells as like you say I wouldn't know what date is the wrong one, two or three. It will be a highlighted on the header then we can see there is a mismatch on that milestone and investigate further and correct it.

Hi
It's becoming a bit clearer. If you just wish to highlight the header, then try the following conditional formatting formula in cell A1, if your list is in A2:A5.

Please Login or Register  to view this content.

This seems to work for me, but test it out. Fingers crossed.

DBY

Thank you. I think we're getting a little closer. :-)

What it doesn't seem to do is ignore blanks? What seems to happen is if I have 2 blanks the conditional formatting is not activating regardless of a mismatch or not. If I have 1, 3 or 4 blanks the conditional formatting does kick in, again regardless of a mismatch. Obviously 3 or 4 blanks is not an error. If I have 4 dates then the formula works a treat with any combo of mismatch.

Hi
Sorry I couldn't get back yesterday but for some reason I couldn't get the forum to load. Other web pages were fine, very strange. However, yes, the formula I provided was not very good, so try this new one, it seems to be working better.

Please Login or Register  to view this content.

Hope we're getting closer it's a logic problem and my brain doesn't want to compute!

DBY

Not a problem at all, DBY. Just glad someone has replied. :-)

And I think you've cracked it. Yeah, my logic only goes so way. ;-)

Thank you very much for your help! Solved.

For bonus points I was wondering if someone could check me here. The 4 dates relate to 4 "sites". These 4 sites have site refs (which appear on the same tab in a similar block of 4 cells. I'd like the conditional formatting to only activate when these site refs match. Does this formula look correct if we assume the 4 refs are in A2:A5 and the dates are in B2:B5?

Please Login or Register  to view this content.

The complication comes when we have a mixture of matching and mismatching site refs but that's going to be too complex so I'm happy with the above formula that checks for matching refs or not if no one can see any potential problems?