## Convert dates into date ranges in excel

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

### Category: Dates

Create a date range using excel formula

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 […]Comments(142) Filed in category: Dates, Excel

Lookup a value and find max date

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]Comments(85) Filed in category: Dates, Excel

Formula for matching a date within a date range in excel

Table of contents Match a date when a date range is entered in a single cell Match a date when […]Comments(48) Filed in category: Dates, Excel

Finding the nearest date in a range of dates using excel formula

Array formula in E3: =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)) Recommended articles: How to enter an array formula Select cell E3 Type […]Comments(42) Filed in category: Dates, Excel

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 […]Comments(31) Filed in category: Excel, Overlapping

Find overlapping date ranges in excel

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]Comments(24) Filed in category: Excel, Overlapping

Highlight duplicate values and overlapping dates in excel

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]Comments(23) Filed in category: Excel, Overlapping

Count records between two dates with multiple parameters in excel

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]Comments(14) Filed in category: Count values, Dates, Excel

How to sort cells filtered by two dates

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow […]Comments(12) Filed in category: Dates, Excel, Sort values

Visualize date ranges in a calendar

Here is a picture of a simple calendar. I have used conditional formatting to: highlight date ranges (green) highlight possible […]Comments(10) Filed in category: Calendar, Excel, Overlapping

### 7 Responses to “Convert dates into date ranges in excel”

### Leave a Reply

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

<code>your formula</code>

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