Convert date ranges into dates
This article demonstrates how to create a list of dates based on multiple date ranges.
Table of contents
1. Convert date ranges into dates - Excel 365
This section shows an Excel 365 formula that lists dates based on multiple date ranges, it spills values automatically to cells below.
Excel 365 formula in cell B3:
Explaining formula
Step 1 - Calculate days in each date range
The minus sign lets you subtract numbers in an Excel formula.
E4:E8-D4:D8
becomes
and returns
{2;4;0;16;1}.
Step 2 - Create a sequence of numbers from 0 to n
The SEQUENCE function creates a list of sequential numbers.
Function syntax: SEQUENCE(rows, [columns], [start], [step])
SEQUENCE(,x+1,0)
x is a variable in the LAMBDA function that represents each value in the array: {2;4;0;16;1}
The SEQUENCE function creates a new sequence for each number in the array.
Step 3 - Stack arrays vertically
The VSTACK function combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)
Function syntax: VSTACK(array1,[array2],...)
VSTACK(acc,SEQUENCE(,x+1,0))
The VSTACK function adds each sequence to an accumulator variable named acc.
Step 4 - Build LAMBDA function
The LAMBDA function build custom functions without VBA, macros or javascript.
Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)
LAMBDA(acc,x,VSTACK(acc,SEQUENCE(,x+1,0)))
The LAMBDA function lets you name parameters you need in order to properly use the REDUCE function.
Step 5 - Build REDUCE function
The REDUCE function shrinks an array to an accumulated value, a LAMBDA function is needed to properly accumulate each value in order to return a total.
Function syntax: REDUCE([initial_value], array, lambda(accumulator, value))
REDUCE(0,E4:E8-D4:D8,LAMBDA(acc,x,VSTACK(acc,SEQUENCE(,x+1,0))))
returns
Excel fills empty array contains with error values. We need to take care of this in the last step.
Step 6 - Remove first row in array
The DROP function removes a given number of rows or columns from a 2D cell range or array.
Function syntax: DROP(array, rows, [columns])
DROP(REDUCE(0,E4:E8-D4:D8,LAMBDA(acc,x,VSTACK(acc,SEQUENCE(,x+1,0)))),1)
returns the following array:
The first row in the array is deleted. Why is this row in there? It is the first argument 0 in the REDUCE function that creates the first row in the array.
Step 7 - Add start dates to number sequences
The plus sign lets you add numbers in an Excel formula. Excel dates are actually numbers so this method allows us to create date sequences.
D4:D8+DROP(REDUCE(0,E4:E8-D4:D8,LAMBDA(acc,x,VSTACK(acc,SEQUENCE(,x+1,0)))),1)
becomes
and returns
Step 8 - Rearrange array to one column
The TOCOL function rearranges values in 2D cell ranges to a single column.
Function syntax: TOCOL(array, [ignore], [scan_by_col])
TOCOL(D4:D8+DROP(REDUCE(0,E4:E8-D4:D8,LAMBDA(acc,x,VSTACK(acc,SEQUENCE(,x+1,0)))),1))
returns
Step 9 - Extract unique dates
The UNIQUE function returns a unique or unique distinct list.
Function syntax: UNIQUE(array,[by_col],[exactly_once])
UNIQUE(TOCOL(D4:D8+DROP(REDUCE(0,E4:E8-D4:D8,LAMBDA(acc,x,VSTACK(acc,SEQUENCE(,x+1,0)))),1)))
returns
The fourth value is an error value. This value will be last when we sort the array in the next step.
Step 10 - Sort dates from small to large
The SORT function sorts values from a cell range or array
Function syntax: SORT(array,[sort_index],[sort_order],[by_col])
SORT(UNIQUE(TOCOL(D4:D8+DROP(REDUCE(0,E4:E8-D4:D8,LAMBDA(acc,x,VSTACK(acc,SEQUENCE(,x+1,0)))),1))))
returns
Step 11 - Remove last row in array
The DROP function removes a given number of rows or columns from a 2D cell range or array.
Function syntax: DROP(array, rows, [columns])
DROP(SORT(UNIQUE(TOCOL(D4:D8+DROP(REDUCE(0,E4:E8-D4:D8,LAMBDA(acc,x,VSTACK(acc,SEQUENCE(,x+1,0)))),1)))),-1)
The last value in the array is an error value, the DROP function removes the last value from the array.
2. Convert date ranges into dates - earlier Excel versions
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it handles overlapping date ranges, however, only one instance of each overlapping date is shown.
Array formula in cell B3:
How to create an array formula
- Copy above array formula
- Select cell B3
- Press with left mouse button on in formula bar
- Paste array formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter
- Release all keys
How to copy array formula
- Select cell B3
- Copy cell (Ctrl + c)
- Select cell range B3:B30
- Paste (Ctrl + v)
Explaining formula in cell B3
Step 1 - Create a dynamiccell reference
This step calculates the first date and the last date of all date ranges and builds a cell reference with as many rows as there are days between the first date and the last date.
The MIN function returns the smallest number (date) from all date ranges and the MAX function returns largest number (date) from all date ranges.
$B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1)
becomes
$B$1:INDEX($B:$B, 40210-40179+1)
becomes
$B$1:INDEX($B:$B, 32)
and returns
$B$1:$B$32
Step 2 - Create a sequence of values
The ROW function creates a sequence of numbers between 0 (zero) and n based on the number of rows in the cell reference.
ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1
becomes
ROW($B$1:$B$32)-1
and returns
{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 3 - Create an array of dates
"<="&MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1
becomes
"<="&MIN($D$3:$E$7)+{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}
The MIN function returns the smallest number (earliest date).
"<="&MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1
becomes
"<="&MIN($D$3:$E$7)+{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}
becomes
"<="&40179+{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}
becomes
"<="&{40179; 40180; 40181; 40182; 40183; 40184; 40185; 40186; 40187; 40188; 40189; 40190; 40191; 40192; 40193; 40194; 40195; 40196; 40197; 40198; 40199; 40200; 40201; 40202; 40203; 40204; 40205; 40206; 40207; 40208; 40209; 40210}
and returns
{"<=40179"; "<=40180"; "<=40181"; "<=40182"; "<=40183"; "<=40184"; "<=40185"; "<=40186"; "<=40187"; "<=40188"; "<=40189"; "<=40190"; "<=40191"; "<=40192"; "<=40193"; "<=40194"; "<=40195"; "<=40196"; "<=40197"; "<=40198"; "<=40199"; "<=40200"; "<=40201"; "<=40202"; "<=40203"; "<=40204"; "<=40205"; "<=40206"; "<=40207"; "<=40208"; "<=40209"; "<=40210"}
The less than sign and equal sign is there to check if dates are inside the date range, this is calculated in the next step.
Step 4 - Calcualte if date is in date range
The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.
COUNTIFS($D$3:$D$7, "<="&MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1, $E$3:$E$7, ">="&MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1)
returns
{1; 2; 2; 1; 1; 1; 1; 1; 1; 0; 1; 0; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 0; 1; 1}
A 0 (zero) indicates that the date is not in a date range, 1 means that the date is in one date range, 2 means two dates ranges and so on.
Step 5 - Replace any number except 0 (zero) with corresponding date
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
IF(COUNTIFS($D$3:$D$7, "<="&MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1, $E$3:$E$7, ">="&MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1), MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1, "")
becomes
IF({1; 2; 2; 1; 1; 1; 1; 1; 1; 0; 1; 0; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 0; 1; 1}, MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1, "")
becomes
IF({1; 2; 2; 1; 1; 1; 1; 1; 1; 0; 1; 0; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 0; 1; 1}, {40179; 40180; 40181; 40182; 40183; 40184; 40185; 40186; 40187; 40188; 40189; 40190; 40191; 40192; 40193; 40194; 40195; 40196; 40197; 40198; 40199; 40200; 40201; 40202; 40203; 40204; 40205; 40206; 40207; 40208; 40209; 40210}, "")
and returns
{40179; 40180; 40181; 40182; 40183; 40184; 40185; 40186; 40187; ""; 40189; ""; 40191; 40192; 40193; 40194; 40195; 40196; 40197; 40198; 40199; 40200; 40201; 40202; 40203; 40204; 40205; 40206; 40207; ""; 40209; 40210}.
Step 6 - Extract k-th smallest date
To be able to return a new value in a cell each I use the SMALL function to filter date numbers from smallest to largest.
SMALL(IF(COUNTIFS($D$3:$D$7, "<="&MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1, $E$3:$E$7, ">="&MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1), MIN($D$3:$E$7)+ROW($B$1:INDEX($B:$B, MAX($D$3:$E$7)-MIN($D$3:$E$7)+1))-1, ""), ROWS($B$1:B1))
becomes
SMALL({40179; 40180; 40181; 40182; 40183; 40184; 40185; 40186; 40187; ""; 40189; ""; 40191; 40192; 40193; 40194; 40195; 40196; 40197; 40198; 40199; 40200; 40201; 40202; 40203; 40204; 40205; 40206; 40207; ""; 40209; 40210}, ROWS($B$1:B1))
becomes
SMALL({40179; 40180; 40181; 40182; 40183; 40184; 40185; 40186; 40187; ""; 40189; ""; 40191; 40192; 40193; 40194; 40195; 40196; 40197; 40198; 40199; 40200; 40201; 40202; 40203; 40204; 40205; 40206; 40207; ""; 40209; 40210}, 1)
and returns 1/1/2010 in cell B3.
Useful links
Format an Excel date the way you want
How to change date format in Excel and create custom formatting
Dates category
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
Excel categories
7 Responses to “Convert date ranges into dates”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Wow this is for the Excel Black belt guys!
Oscar for the benefit of those who aspire to get here someday, could you please explain in brief the logic and what part of the formula is doing what.......
chrisham,
Thanks!!
I will explain this post and all the others as soon as I can. This post is now number one on my update list.
Any ideas why - if I enter 20/7/12 to 30/7/12 the range is expanded from 24/7/12 to 30/7/12 (and does not start at the 20th)?
resolved - not sure what the problem was though
Wouldn't be easier to use new 365 functions?
JohnH,
Yes, of course. Thank you for telling me, I will update this.