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.