Excel - Checking if any day of specified month falls between a range of dates

Question

I have a worksheet with dates in columns A and B:

   col A   |    col B   |

2014-06-30 | 2014-10-31 | 
2014-01-21 | 2014-05-31 |
2012-03-07 | 2014-11-03 |
2013-02-05 | 2014-05-01 |

In column C I want to check if any day of specified month (i.e. June 2014) falls between dates form columns A and B:

   col A   |    col B   |   col C  |

2014-06-30 | 2014-10-31 |    yes   |
2014-01-21 | 2014-05-31 |    no    |
2012-03-07 | 2014-11-03 |    yes   |
2013-02-05 | 2014-05-01 |    no    |

Any ideas? (VBA or Excel formula)

Thank you in advance for your help!

Answer 1

There would only not be an overlap if the first day of that month is later than B, or if last day is before A, so this formula should work

=IF(OR(A2>DATE(2014,6,30),B2<DATE(2014,6,1)),"no","yes")

To shorten you can put the start and end dates of the reference period in two cells, e.g. E1 and F1 and then use this version:

=IF(OR(A2>F$1,B2<E$1),"no","yes")

Answer 2

Assuming Excel knows that the first to columns are dates; lets say you put your "specified month" in column D, this is how you can compare it:

=IF(AND(D1>A1,D1<B1),"yes","no")

It would have to compare another date, like if you wanted to test July you would reference a cell with 7-1-2014

In order to be more specific you would probably want to use VBA and date references to check.