A coworker of mine, who works in another department, had a spreadsheet her group uses to manage what gets pushed out to the stores and when. If you’re in retail, and retail IT especially, you know you don’t mess with the stores anymore than necessary. They are the moneymakers, and you don’t want to be the guy responsible for bringing down their systems to where they can’t sell anything.
This spreadsheet has several columns of dates, with each date corresponding to a specific piece of software that will get rolled out or to which a new release gets pushed. These dates should never overlap. No store gets two pushes in the same day.
This leads me to the challenge they had. With so many multiple columns of dates, they wanted a flag of some sort that would pop up if the same date was entered more than once for that record.
Yeah, I can solve that, I thought. And I did. Here’s how.
Using a mock-up file as a playground, I created four columns of dates. Next I added a fifth column called # Fields Used. In this column I added just a straight count formula. If for some reason any of the columns does not have an entry, I don’t want it counted. It’s always possible that some stores have some software that other stores do not.
The last column is the Overlap column. Into this column I inserted the following formula:
=IF((COUNTIF(B2:E2,B2)+COUNTIF(B2:E2,C2)+COUNTIF(B2:E2,D2)+COUNTIF(B2:E2,E2))>F2,”OVERLAPPING DATES”,””)
Essentially what I’m doing is counting each instance that the value in each column occurs using a COUNTIF. Then I’m adding them all together to be certain I account for all instances. Granted, that creates a double count, but who cares? I’m already over the limit, so over by a little or by a lot, it’s all the same.
If the count for all the columns where the value equals the first one is more than one, which we want it never to be, then it triggers the TRUE part of the IF statement, in which case I’m writing text to the cell that says “Overlapping Dates”. You could also add some conditional formatting to give it some pop, but I skipped that part in the screen grab below.
There is probably a better way of doing this, maybe utilizing FREQUENCY. Truth be told, I didn’t have a lot of time, and this one involved a pretty easy formula to modify, so I went with it. If any had a better method, I’d love to hear about it.
Scott C Lyerly 
Hi Scott,
Often an immediate fix is more important than calculation speed, especially if the data set is not too large. So your solution works fine. Your guess that the FREQUENCY() function might be a more efficient solution is correct, but FREQUENCY() needs a little help from MAX(). Below Is my solution using MAX() and FREQUENCY() with the first data cell in A3.
The same formula is in both the Overlap Count and Custom Format columns. The formula in both E3 and F3 is =MAX(FREQUENCY($A3:$D3,$A3:$D3)).
Overlap Count is the raw output of the MAX() and FREQUENCY() formula. Overlap is any number greater than 1.
Custom Format is the same result with a custom number format of “[>1]”Overlap”;;” (the extra semicolons are to hide any other results).
Only one of these columns is needed I show both purely to demonstrate the raw and custom format results.
Overlap Custom
Date 1 Date 2 Date 3 Date 4 Count Format
01 May 01 May 24 May 18 May 2 Overlap
30 May 13 May 29 May 10 May 1
12 May 16 May 12 May 2 Overlap
0
07 May 24 May 30 May 24 May 2 Overlap
25 May 25 May 2 Overlap
29 May 07 May 18 May 19 May 1
07 May 07 May 11 May 2 Overlap
11 May 26 May 31 May 1
13 May 04 May 20 May 11 May 1
If you subtract 1 from the formula =MAX(FREQUENCY($A3:$D3,$A3:$D3)).-1, any overlap will be greater than 0 and the custom number format can be a simpler “”Overlap”;;”
Overlap Custom
Date 1 Date 2 Date 3 Date 4 Count Format
01 May 01 May 24 May 18 May 1 Overlap
30 May 13 May 29 May 10 May 0
12 May 16 May 12 May 1 Overlap
-1
07 May 24 May 30 May 24 May 1 Overlap
25 May 25 May 1 Overlap
29 May 07 May 18 May 19 May 0
07 May 07 May 11 May 1 Overlap
11 May 26 May 31 May 0
13 May 04 May 20 May 11 May 0
Keep On Excel-ling,
Dave Laverie
Hmmmm … My reply looked fine while I wrote it, but the post came out without the neatly laid out columns I used. Some rows have less than 4 dates because I left a few blank cells in testing, and the rows showing just 0 or -1 have date cells that are all blank. Dave
You could go the Conditional Formatting route too. Use a formula like
=COUNTIF($B2:$E2,B2)>1
and set the Fill color to red for a visual indicator there’s a duplicate.