Date Column Comparison, Odd One Out

[Alex_Hayden]

I am currently scouring and plugging numbers from years prior into a spreadsheet for graphical purposes and as such have numerous date based cells assigned individualized data. Now, the data points I am pulling from aren't exactly what you would call 'consistent' and miss quite a few days here and there. I was wondering if there was any way I could compare two columns containing dates from the aforementioned years and find their differences despite the difference in year number (2014 vs 2013, etc etc). I know that this is possible by hand, but I very much do not want to take the time to scan through multiple years worth of points to find missing dates (nightmarish when I consider the possibility of me skipping over a missing point unintentionally) if there is a simpler formula based way to do it.

The formatting on my date columns is mm/dd/yy, but if necessary I can take the time to trim down each column to mm/dd (the only immediate solution I could think of that allowed easy comparison). Any and all help is greatly appreciated.

 

[Sinon]

What I do when I want to find the odd one out is use countif. Your issue is that the dates are different due to years. Try using the DATE function to temporarily assign a different year to your dates. For instance, go to your 2014 dates, assuming they are in column A, and apply this formula:
=DATE(2013,MONTH(A2),DAY(A2))
This formula will simply assign a different year to your dates, while keeping the date and month. You can do this for your 2013 data and replace the year to 2014.

Your next step is to do a count based on this new date. The idea is to see whether all your 2014 dates (that now have 2013 due to the DATE function) are present in your 2013 dates.
=COUNTIF('2013 sheet'!A:A,'2014 sheet'!B2)
Anything that's 0 means that that particular date is not present in your 2013 data.
Do the same for the 2013 data to see what might be missing in 2014 that you had in 2013.
I hope this makes sense. let me know if it helped.

 

[Alex_Hayden]

I understand what you are saying, and I've implemented the date conversion in a separate column so that its not self cell referencing, but I get lost at the countif function due to lack of knowledge. Is the countif taking columns and comparing them? Do these have to be on differing sheets? I've never used the function before being fairly new to 'higher level' Excel, and don't really have a lot of the syntax down yet.
Below is an image of what I'm working with:

 

[Alex_Hayden]

Column Y references Column AI for it's year conversion formula, if that's something that might be useful to know.

 

[Sinon]

As long as you have the date down, that's solving half of the issue. For the countif have a look here: https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34

I also made a quick sample with dates for you, see here: https://www.dropbox.com/s/0d0kos1yfxmqqm5/COUNTIF.xlsx?dl=0

Hope it makes sense

 

[Alex_Hayden]

With that visualization there I completely understand how its supposed to work and whatnot. Again, thanks a lot, you've been nothing but helpful!