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 in cell E4 extracts the end dates for date ranges in cell range B3:B30.
The dates in column B must be sorted from smallest to largest for the formula to work properly, the image above shows the date ranges (blue lines) and the numbers correspond to the extracted date ranges displayed in column D and E.
Array formula in cell D4:
How to create an array formula
- Select cell C2
- Paste formula
- Press and hold CTRL + SHIFT
- Press Enter
Copy cell C2 and paste it down as far as needed.
Array formula in cell E4:
Copy cell D2 and paste it down as far as needed.
Explaining formula in cell D5
The formula extracts the first date if the current cell is the first cell that is why I start with formula in cell D5.
The ROWS function returns the number of rows in a cell reference, when the cell is copied to cells below the cell reference grows, this is how the formula keeps track of where the first cell is.
IF(ROWS($B$2:B2)=1,INDEX($B$3:$B$30,1), formula)
ROWS($B$2:B2)=1
becomes
1=1 equals TRUE.
IF(TRUE,INDEX($B$3:$B$30,1), formula)
becomes
INDEX($B$3:$B$30,1)
and returns the first date in cell $B$3:$B$30 (1/1/2010).
Step 1 - Compare dates
If we subtract a date with the next date in a sorted list we can quickly check if the next date is one day after the first date. If the number is larger than 1 we know the first date must be an end date and the next date must be a start date.
$B$4:$B$30-$B$3:$B$29>1
becomes
{40180; 40181; 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} - {40179; 40180; 40181; 40183; 40184; 40185; 40186; 40187; 40189; 40191; 40192; 40193; 40194; 40195; 40196; 40197; 40198; 40199; 40200; 40201; 40202; 40203; 40204; 40205; 40206; 40207; 40209}>1
becomes
{1; 1; 2; 1; 1; 1; 1; 2; 2; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 2; 1}>1
and returns
{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}
Step 2 - Replace TRUE with corresponding row number
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($B$4:$B$30-$B$3:$B$29>1,ROW($B$3:$B$29)-MIN(ROW($B$3:$B$29))+2,"")
becomes
IF({FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE},ROW($B$3:$B$29)-MIN(ROW($B$3:$B$29))+2,"")
becomes
IF({FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE},{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},"")
and returns
{""; ""; 4; ""; ""; ""; ""; 9; 10; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 27; ""}.
Step 3 - Extract k-th smallest row number
The SMALL function lets you calculate the k-th smallest value in a cell range or array. SMALL( array, k). This makes sure that a new date is displayed in each cell below. The k argument contains an expanding cell reference that grows when the cell is copied to cells below.
SMALL(IF($B$4:$B$30-$B$3:$B$29>1, ROW($B$3:$B$29)-MIN(ROW($B$3:$B$29))+2, ""), ROWS($B$1:B2)-1)
becomes
SMALL({""; ""; 4; ""; ""; ""; ""; 9; 10; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 27; ""}, ROWS($B$1:B2)-1)
becomes
SMALL({""; ""; 4; ""; ""; ""; ""; 9; 10; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 27; ""}, 1)
and returns 4.
Step 4 - Return value
The INDEX function returns a value based on a cell reference and column/row numbers.
INDEX($B$3:$B$30,SMALL(IF($B$4:$B$30-$B$3:$B$29>1,ROW($B$3:$B$29)-MIN(ROW($B$3:$B$29))+2,""),ROWS($B$1:B1)-1))
becomes
INDEX($B$3:$B$30, 4)
and returns 1/5/2010 in cell D5.
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 Download [โฆ]
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 [โฆ]
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
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in [โฆ]
Use MEDIAN function to calculate overlapping ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, [โฆ]
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 [โฆ]
Identify rows of overlapping records
cwrbelis asks: Hi Oscar, Great website! Keep up the good work. I have a question as to how to expand [โฆ]
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 [โฆ]
7 Responses to โConvert dates into date rangesโ
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.
English
Spanish
Here is a way to do it with shorter formulas, but you have to be willing to use only Column C's #NUM! error to identify when to stop looking (Column D's value will repeat the previous rows value at this location).
Put =A2 in cell C2, then put this array-entered** formula in C3 and copy it down...
=SMALL(IF(A$3:A$100-A$2:A$99>1,(A$3:A$100-A$2:A$99>1)*A$3:A$100),ROW(A1))
Next, put this array-entered** formula in D2 (note, this is D2, not D3) and copy it down...
=SMALL(IF(A$3:A$100-A$2:A$99>1,(A$3:A$100-A$2:A$99>1)*A$2:A$99,LOOKUP(2,1/(A$1:A$65535""),A:A)),ROW(A1))
The top ends of the offsetted ranges (A$99 and A$100) can be any cell address that is equal to or larger than the cell address of the last date.
**Commit this formula using Ctrl+Shift+Enter, not just Enter by itself
A follow up to my previous post...
The last paragraph (right before the double-asterisk note) does not read exactly right; perhaps this is better...
The top ends of the offsetted ranges (A$2:A$99 and A$3:A$100) can be any cell addresses, still offsetted by one, that is equal to or larger than the cell address of the last date. The key to keep in mind is the the number of cells in each of the two ranges must be the same because this is an array-entered formula and, as such, the iteration process inherent in an array-entered formula requires a cell-for-cell correspondence.
Very good, both formulas are shorter!
Thank you for your contribution and explanation!
Is there a way to reverse this formula to Convert date ranges into dates in excel. For example: from 1 March 2010 - 28 February 2011 to convert that in to list from A1 to A365 starting from 1 March 2010 and A2 2 March 2010 ans so on until 28 February 2011?
Tiaan,
read this post: Convert date ranges into dates in excel
Dear Oscar,
Thanks for the last post, I'm rely grateful for your help. I have another query regarding date format.
I have an excel sheet showing date as 11-08-2018, BUT
I want to convert this date FORMAT to 18-08-2011.
Your help in this matter shall be highly appreciated.
Cheers!
Muhammad Nadeem Bhatti,
I am not sure how to solve it, try google:
excel yy/mm/dd dd/mm/yy