## Convert date ranges into dates

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.

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 latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Get […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

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

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

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 […]

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 […]

List all unique events in a month

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]

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 […]

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 […]

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

**Contact Oscar**

You can contact me through this contact form

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