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:

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

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

Explaining array formula in A2

Step 1 - Create array

Let us start with the bolded part of the formula.

=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))

ROW(OFFSET($A$1, , 1, MAX($D$2:$D$6)-MIN($C$2:$C$6)+1))

$D$2:$D$6 is an absolute cell reference and contains 3-Jan-2010, 9-Jan-2010, 11-Jan-2010,29 -Jan-2010, 1-Feb-2010.

MAX($D$2:$D$6)

becomes

MAX(3-Jan-2010, 9-Jan-2010, 11-Jan-2010,29 -Jan-2010, 1-Feb-2010) and returns 1-Feb-2010.

Cell reference $C$2:$C$6 is 3-Jan-2010, 5-Jan-2010, 11-Jan-2010, 13-Jan-2010, 31-Jan-2010.

MIN($C$2:$C$6)

becomes

MIN(1-Jan-2010, 5-Jan-2010, 11-Jan-2010, 13-Jan-2010, 31-Jan-2010) and returns 1-Jan-2010.

MAX($D$2:$D$6)-MIN($C$2:$C$6) is 1-Feb-2010 - 1-Jan-2010 and equals 31

OFFSET($A$1, , 1, MAX($D$2:$D$6)-MIN($C$2:$C$6)+1)

becomes

OFFSET($A$1, , 1, 1-Feb-2010 - 1-Jan-2010+1)

becomes

OFFSET($A$1, , 1, 31+1)

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

becomes

$A$1:$A$32

becomes

ROW($A$1:$A$32) -1 returns this array: {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24 ,25, 26, 27, 28, 29, 30, 31}

Step 2 - Find smallest date

=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))

MIN($C$2:$C$6) returns 1-Jan-2010

Step 3 - Create date array

MIN($C$2:$C$6)+ROW(OFFSET($A$1, , 1, MAX($D$2:$D$6)-MIN($C$2:$C$6)+1))-1

becomes

1-Jan-2010 + {0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24 ,25, 26, 27, 28, 29, 30, 31}

equals

{1-Jan-2010, 2-Jan-2010, 3-Jan-2010, 4-Jan-2010, 5-Jan-2010, 6-Jan-2010, 7-Jan-2010, 8-Jan-2010, 9-Jan-2010, 10-Jan-2010, 11-Jan-2010, 12-Jan-2010, 13-Jan-2010, 14-Jan-2010, 15-Jan-2010, 16-Jan-2010, 17-Jan-2010, 18-Jan-2010, 19-Jan-2010, 20-Jan-2010, 21-Jan-2010, 22-Jan-2010, 23-Jan-2010, 24-Jan-2010, 25-Jan-2010, 26-Jan-2010, 27-Jan-2010, 28-Jan-2010, 29-Jan-2010, 30-Jan-2010, 31-Jan-2010}

Step 4 -

To be continued...

Download excel sample file for this tutorial.

Create-dates-from-date ranges.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

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.

TRANSPOSE(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