Go Back

Create date range from two dates

When we have two dates in two different cell references and wish to display them in one cell as date range as per our desired format then we will learn how to do that in this article. Using a formula based on the TEXT function and Ampersand (&), concatenating operator, we can create date range from two dates in Excel. This article will step through the process.

Figure 1. Creating Date Range From Two Dates

Formula Syntax

The generic syntax for the formula is as follows;

=TEXT(date1,"format")&" - "&TEXT(date2,"format")

Suppose we have the start date in cell B2 and the end date in cell C2. We want to display these both dates in a cell E2 as date range as per a custom date format “mmm d”. Following the above formula syntax, we can create date range from two dates in a single formula, such as;

=TEXT(B2,"mmm d")&" - "&TEXT(C2,"mmm d")

Figure 2. Applying the Formula to Create Date Range

How Formula Works

When we have a date value, the TEXT function returns it as a custom date format. By using the TEXT function we can return these two date values in a custom format and with the help of Ampersand (&), concatenating operator, we can combine these two formats in a single formula. After applying the formula, press Enter and copy the formula down to other cells.

Figure 3. Displaying Date Range in Single Cell

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

hello i have the range of dates and the range of serial numbers respective to the date. I want to find what is the last recorded date of repeated serial number from date range.
Solved by C. Y. in 18 mins
Hello I'm looking to create a sales sheet using the front master sheet, with two data sheets for two different brands behind it. I need to be able to pull column data through by the date range shown at the top, which needs to be changeable. The data on the sheets behind will end up being year to date. Number enquiries = all data within the range. Total number of appointments within range = range with number of dates populated in column I. Number of sold and sold process later within range. Chris
Solved by F. H. in 19 mins
Hello I've just had two sessions and this is the next. I'm looking to create a sales sheet using the front master sheet, with two data sheets for two different brands behind it. I need to be able to pull column data through by the date range shown at the top, which needs to be changeable. The data on the sheets behind will end up being year to date. Number enquiries = all data within the range. Total number of appointments within range = range with number of dates populated in column I. Number of sold and sold process later within range. Chris
Solved by M. H. in 16 mins

Leave a Comment

avatar