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.