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.
Leave a Comment