Geek Answers Handbook

If the date range matches another date range

I have a data set with the 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 will be on vacation during the block dates. He must return 4; Joe, David, Patty, And Megan.

The formula I am:

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

colStartand colEndare called the "Start and End" column ranges.

B1and B2- these are the block dates on my spreadsheet.

, -, .. - . Joe, .. 10 Jan. .

. .

+4
3

.

, B2 , B1, - - , ,

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

+4

, . , .

  • .

.

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

Suppose the start date link is on B1, and the end date link is on B2.

Also note that the header of the dataset begins on cell A4 (Name), and the first row of data is # 5.

Enter in D5 (and drag the formula down):

=IF(OR(AND(B5>$B$1,B5<$B$2),AND(C5>$B$1,C5<$B$2),AND(B5<$B$1,C5>$B$2)),1,0)

Then in any cell:

=SUM(D5:D9)
+1
source

More articles:


All Articles