Google Sheets Formula – Compute Cardinality of Intersection of Date Sets

dateformulasgoogle sheets

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.

Best Answer

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) ''"
)
Related Topic