Convert date ranges into dates

Author: Oscar Cronquist Article last updated on December 23, 2018

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:

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

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

and returns 1/1/2010 in cell B3.

Get the Excel file

Create-dates-from-date-ranges2-1.xlsx

Dates category

Create a date range [Formula]
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 […]

Find the most recent date that meets a particular condition
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

Formula for matching a date within a date range
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

Lookup the nearest date
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Identify overlapping date ranges
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]

List all unique distinct rows in a given month
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

Lookup min max values within a date range
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]

How to calculate overlapping time ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

Extract unique distinct year and months from dates
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

Convert dates into date ranges
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

Identify rows of overlapping records
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

Count overlapping days across multiple date ranges
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

Filter unique distinct values based on a date range
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]

Create a list of dates with blanks between quarters
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

Count overlapping days in multiple date ranges, part 2
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

Find date range based on a date
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]

List dates outside specified date ranges
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]

Functions in this article

SMALL IF COUNTIFS MAX MIN ROWS

More than 1300 Excel formulas

Excel formula categories

Average Choose Combine Merge Compare Concatenate Conditional Formatting Count Count values Dates Duplicates Extract Filter Filter records Filtered values Filterxml Hyperlink If cell Index Match Logic Lookups Match Max Min Numbers in sum OR logic Overlapping Partial match Records Sequence Small Sort by Sort values Sum Sumifs Sumproduct String manipulation Time Unique distinct records Unique distinct values Unique records Unique values Vlookup Vlookup return values

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

5 Responses to “Convert date ranges into dates”

chrisham says:

July 5, 2010 at 4:37 am

Wow this is for the Excel Black belt guys!

Reply
chrisham says:

July 5, 2010 at 4:54 am

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.......

Reply
Oscar says:

July 5, 2010 at 10:07 pm

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.

Reply
Kevin says:

August 30, 2012 at 11:51 am

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

Reply
- Kevin says:
  
  August 30, 2012 at 12:16 pm
  
  resolved - not sure what the problem was though

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.

Contact Oscar

You can contact me through this contact form