In a previous post I created a formula to convert dates into date ranges. Now it is time to create dates from date ranges.

The date ranges are in cells C2:D6. The date list in column A is created by an array formula.

Convert date ranges into dates

Array formula in A2:

=SMALL(IF(FREQUENCY(IF((MIN($C$2:$C$6)+ROW(OFFSET($A$1, , 1, MAX($D$2:$D$6)-MIN($C$2:$C$6)+1))-1<=TRANSPOSE($D$2:$D$6))*(MIN($C$2:$C$6)+ROW(OFFSET($A$1, , 1, MAX($D$2:$D$6)-MIN($C$2:$C$6)+1))-1>=TRANSPOSE($C$2:$C$6)), MIN($C$2:$C$6)+ROW(OFFSET($A$1, , 1, MAX($D$2:$D$6)-MIN($C$2:$C$6)+1))-1, ""), MIN($C$2:$C$6)+ROW(OFFSET($A$1, , 1, MAX($D$2:$D$6)-MIN($C$2:$C$6)+1))-1)=1, MIN($C$2:$C$6)+ROW(OFFSET($A$1, , 1, MAX($D$2:$D$6)-MIN($C$2:$C$6)+1))-1, ""), ROW(A1)


Smaller array formula:

=SMALL(IF(COUNTIFS($C$2:$C$6, "<="&MIN($C$2:$D$6)+ROW($A$1:INDEX($A:$A, MAX($C$2:$D$6)-MIN($C$2:$D$6)+1))-1, $D$2:$D$6, ">="&MIN($C$2:$D$6)+ROW($A$1:INDEX($A:$A, MAX($C$2:$D$6)-MIN($C$2:$D$6)+1))-1), MIN($C$2:$D$6)+ROW($A$1:INDEX($A:$A, MAX($C$2:$D$6)-MIN($C$2:$D$6)+1))-1, ""), ROW(A1))

How to create an array formula

  1. Copy above array formula
  2. Select cell A2
  3. Click in formula bar
  4. Paste array formula in formula bar
  5. Press and hold Ctrl + Shift
  6. Press Enter
  7. Release all keys

How to copy array formula

  1. Select cell A2
  2. Copy cell (Ctrl + c)
  3. Select cell range A3:A30
  4. Paste (Ctrl + v)

There can´t be overlapping date ranges in column C and D.

The new array formula handles overlapping dates!

Download excel sample file for this tutorial.

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


(New formula and excel *.xlsx file)

Functions in this article:

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.

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

ROW(reference) returns the rownumber of a reference

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.

Converts a vertical range to a horizontal range, or vice versa.

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference