< Go Back

Create date range from two dates

How to create date range from two dates

If you have two dates in different cells and you wish to display them in one cell as a date range, how do you that? Well, you just need to apply a simple excel formula based on the TEXT function. This is how the formula looks like:

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

….where “date1” is the first day in the first column while “date2” is the date in the 2nd column. “Format” is the format of the date that is readable by TEXT.

Formula Application: Example

Assume that you have a number of functions that will be taking place on several date ranges. But you have these individual dates on cells. How do you put the date range into one excel cell? This how TEXT function can be help solve the problem:

Figure 1. Example 1 of TEXT Function

From the example, the formula of the highlighted cell (F7) is:

=TEXT(C7,"mmm d")&"-"&TEXT(D7,"mmm d")

How Text Formula Works

As seen from this example, one thing is missing- the year. That is because the TEXT function works by taking the numeric values in the dates and converting then to test based on your preferred text. Take for example the above case. The format is “mmm d” for the two TEXT functions in the entire column F.  The result in cell F7 and the rest, are joined with a hyphen (-) using a simple series.

However, if the of the dates, either start date or end date is missing, the formula will not work correctly. In such cases, you will need to introduce IF function in the formula.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar