Question: How do I convert a list of dates into date ranges?


Create date ranges from a list of dates

Array formula in cell C2:

=IF(ROWS($A$1:A1)=1, INDEX($A$2:$A$29, 1), INDEX($A$2:$A$29, SMALL(IF($A$3:$A$29-$A$2:$A$28>1, ROW($A$3:$A$29)-MIN(ROW($A$3:$A$29))+2, ""), ROW(A1)-1)))

How to create an array formula

  1. Select cell C2
  2. Paste formula
  3. Press and hold CTRL + SHIFT
  4. Press Enter

Copy cell C2 and paste it down as far as needed.

Array formula in cell D2:

=IF(SUM(IF($A$3:$A$29-$A$2:$A$28>1, 1, ""))=ROW(A1)-1, INDEX($A$2:$A$29, COUNT($A$2:$A$29)), INDEX($A$2:$A$28, SMALL(IF($A$3:$A$29-$A$2:$A$28>1, ROW($A$3:$A$29)-MIN(ROW($A$3:$A$29))+1, ""), ROW(A1))))

Copy cell D2 and paste it down as far as needed.

The date list must be sorted oldest to newest and no blanks are allowed.

Download excel sample file for this tutorial.

Create date ranges from specific dates.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test, [value_if:true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Returns the k-th smallest number in this data set.

Returns the smallest number in a set of values. Ignores logical values and text

Returns the rownumber of a reference

ROWS(array) returns the number of rows in a reference or an array

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Counts the number of cells in a range that contain numbers

Adds all the numbers in a range of cells