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.