# Formula to compute the cardinality of the intersection of two sets of dates

Given:

• Two dates, the bounds of a date range;
• A list of dates (given in-extenso in a line or a column);

Is there a way, in Google Spreadsheet, to get the number of dates (days) in the date range that are included in the list of dates ?

Example:

[2017-01-01;2017-01-31] intersected with {2017-01-01, 2017-03-01} would yield 1


Because the range on the left hand side, seen as a set, contains the 31 days of January {2017-01-01, ..., 2017-01-31} and the set on the right hand side contains the date 2017-01-01, which is a day of January.

Assume that:

A1: Holds the start date
A2: Holds the end date
Column B holds the list of dates to count

=QUERY(
B:B,
"select COUNT(B) where B >= date '"&
TEXT(A1,"yyyy-mm-dd")&
"' AND B <= date '"&
TEXT(A2,"yyyy-mm-dd")&
"' label COUNT(B) ''"
)

• Works, thanks, just had to replace commas with semi-colons. – Cans Apr 9 '17 at 10:26