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

Answer:

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))) + CTRL + SHIFT + 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)))) + CTRL + SHIFT + ENTER.

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

SMALL(array,k) returns the k-th smallest row number in this data set.

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

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

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

SUM(number1,[number2],)
Adds all the numbers in a range of cells