compare dates and match months intervals

[thedrinkerparadox]

Hey,
I have a lot of data sets with dates, they need to be matched so spot overlap. I need to know if the dates are within the same months intervals.
See attachment for example data

[Glenn Kennedy]

So what would your expected results be? How awould they be arranged? Please populate your results with some manually calculated results as it is not clear what you want and where you want it...

[thedrinkerparadox]

ok trying again. New file.

[Glenn Kennedy]

God, but I haven't a clue what you want.

Are the 10 dates in each table 10 individual dates or 5 pairs of dates?

Why does cell S5 contain a "1" when NONE of the dates in data 2 are within (at least) a month of May 2016?

I don't do guessing games. One last chance... Please explain how you derive these results.

[thedrinkerparadox]

God, but I haven't a clue what you want.

Are the 10 dates in each table 10 individual dates or 5 pairs of dates?

Why does cell S5 contain a "1" when NONE of the dates in data 2 are within (at least) a month of May 2016?

I don't do guessing games. One last chance... Please explain how you derive these results.

The range 25.05.2016-06.06.2016 (from C3 and D3) has 25,26,27,28,29,30,31 within the 5th month (may). So i just put 1 to show that minimum 1 of the days in the range is in that month.

[Glenn Kennedy]

OK. Got it. I understand now...

[Glenn Kennedy]

Try this in O5, copied across:
=SUMPRODUCT(($C$3:$C$7<=DATE($N$4,MONTH(1&O$4)+1,1))*($D$3:$D$7>=DATE($N$4,MONTH(1&O$4),1)))

and this in O6, copied across:
=SUMPRODUCT(($C$3:$C$7<=DATE($N$4,MONTH(1&O$4)+1,1))*($D$3:$D$7>=DATE($N$4,MONTH(1&O$4),1)))

returns the number of overlaps. If you want some other sort of value returned, it should be easy...

[thedrinkerparadox]

Thanks very much is there a way to only put 1 if as the value even if the periods overlap?

[thedrinkerparadox]

From this i can with my minimum skills in excel find out and calculate that dataset 1 and dataset 2 months dont match 100 %. But are there a way to check how many months of dataset 2 that dont match with dataset 1 months?

[Glenn Kennedy]

Does this answer your Q at Post 8?

I don't quite understand what you want at Post 9.

[thedrinkerparadox]

Thanks very much Glenn Kennedy.
Nevermind the other question this was very helpful!

[Glenn Kennedy]

Aguess for your Q at post 9...

[thedrinkerparadox]

Aguess for your Q at post 9...

Perfect! thanks! :D

[Glenn Kennedy]

Woo Hoo!!!

you're welcome.