Count if date range fits within other date range

Question

I have a set of data with names of people who are on vacation for certain dates (Name, Start, End).

Example:

Name     Start       End
Joe      10-Jan-14   21-Jan-14
Billy    12-Feb-14   10-Mar-14
David    11-Jan-14   20-Jan-14
Patty    10-Jan-14   19-Jan-14
Megan    05-Jan-14   20-Jan-14

-------------------------------
Block    15-Jan-14   22-Jan-14

I want to count the number of people in the range who are going to be on vacation during the block dates. It should return 4; Joe, David, Patty and Megan.

The formula I have is:

=COUNTIFS(colStart, ">="&B1, colEnd, "<="&B2) 

colStart and colEnd are named ranges of the Start and End column.

B1 and B2 are the block dates on my spreadsheet.

My problem is that it won't count someone who started before the block start date or ends after the block end date.. even though they will be on vacation during some point in the block dates. Joe, for example.. starts on 10 Jan. He will be on vacation still during the block dates.

Visually I am comparing number lines. I just need help to translate that to code.

Answer 1

Just turn your formula round a little.

If the start date is equal to or earlier than B2 AND the end date is greater than or equal to B1 then there must be some sort of overlap of the periods - if one or other of those conditions doesn't hold then there is no overlap, hence this formula gives your count

=COUNTIFS(colStart,"<="&B2,colEnd,">="&B1)

Answer 2

Here's how to view it from the opposite side of Jerry's answer. You have two scenarios that you don't want.

1. Start date after your block
2. End date before your block

Count all the dates and exclude those two.

=COUNT(colStart)-SUMPRODUCT((colStart>blkEnd)+(colEnd<blkStart))