## Convert dates into date ranges

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

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 1/11/2009-1/17/2009 […]

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

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

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

Plot date ranges in a calendar

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]

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

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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