## Convert dates into date ranges

*Article updated on March 13, 2018*

**Question**: How do I convert a list of dates into date ranges?

**Answer:**

### Create date ranges from a list of dates

**Array formula in cell C2:**

**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 D2:**

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

The date list must be sorted oldest to newest and no blanks are allowed.

**Download excel sample file for this tutorial. **

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

**ROWS(**array**)** returns the number of rows in a reference or an array

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**COUNT(**value1;[value2]**)
**Counts the number of cells in a range that contain numbers

**SUM(**number1,[number2],**)
**Adds all the numbers in a range of cells

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]### 7 Responses to “Convert dates into date ranges”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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