Date Range in Excel Formula
There are times when we need to perform different operations like addition or subtraction on date values with Excel. By setting date ranges in Excel, we can perform calculations on these dates. For setting date ranges in Excel, we can first format the cells that have a start and end date as ‘Date’ and then use the operators: ‘+’ or ‘-‘to determine the end date or range duration.
Examples of Date Range in Excel
Example #1 – Basic Date Ranges
Let us see how adding a number to date creates a date range.
Suppose we have a start date in cell A2.
Now, if we add a number, say 5 to it, we can build a date range.
Now when we select cell A3 and type ‘=B2 + 1’.
Copy this cell: B2 and paste it to cell B3; the relative cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1. would change as follows:
So we can see that multiple date ranges can be built this way.
Example #2 – Creating Date Sequence
With Excel, we can easily create several sequences. Now we know that dates are some numbers in Excel. So we can use the same method to create date ranges. So to create date ranges which have the same range or gap but the dates change as we go down, we can follow the below steps:
- Type a start date and end date in a minimum of two rows.
- Select both the ranges and drag it down below till the row where we require the dates ranges:
So we can see that using the date ranges in the first two rows as a template, Excel automatically creates date ranges for the subsequent rows.
Now, let’s say we have two dates in two cells, and we wish to display them concatenated as a date range in a single cell. To do this, a formula based on the ‘TEXT’ function can be used. The general syntax for this formula is as follows:
= TEXT(date1,”format”) & ” – ” & TEXT(date2,”format”)
This function receives two date values as numeric and concatenates these two dates in the form of a date range according to a custom date format (“mmm d” in this case):
Date Range =TEXT(A2,”mmm d”) & “-” & TEXT(B2,”mmm d”)
So we can see in the above screenshot that we have applied the formula in cell C2. The TEXT function receives the dates stored in cells A2 and B2, the ampersand ‘&’ operator is used to concatenate the two dates as a date range in a custom format, specified as “mmm d” in this case, in a single cell and the two dates are joined with a hyphen ‘-‘ in the resultant date range which is determined in cell C2.
Now, let’s say in this example, we wish to combine the two dates as a date range in a single cell with a different format, say “d mmm yy’. So the formula for a date range, in this case, would be as follows:
Date Range =TEXT(A3,”d mmm yy”) & “-” & TEXT(B3,”d mmm yy”)
So we can see in the above screenshot that the TEXT function receives the dates stored in cells A3 and B3, the ampersand ‘&’ operator is used to concatenating the two dates as a date range in a custom format, specified as “d mmm yy” in this case, in a single cell. The two dates are joined with a hyphen ‘-‘ in the resultant date range determined in cell C3.
Now let us see what happens in case the start date or end date is missing. Let us say that the end date is missing as follows:
The formula based on the TEXT function that we have used above won’t work correctly in case the end date is missing as the hyphen in the formula would anyhow be appended to the start date, i.e., along with the start date, we would also see a hyphen displayed in the date range. In contrast, we would only wish to see the start date as the date range in case the end date is missing.
So in this case, we can have the formula by wrapping the concatenation and the second TEXT function inside an IF clause as follows:
Date Range =TEXT(A2,”mmm d”) & IF(B2<> “”, “-” & TEXT(B2,”mmm d”), “”)
So we can see that the above formula creates a full date range using both the dates when both are present. However, it displays only the start date in the specified format if the end date is missing. This is done with the help of an IF clause.
Now in case both the dates are missing, we could use a nested IF statement in excelNested IF Statement In ExcelIn Excel, nested if function means using another logical or conditional function with the if function to test multiple conditions. For example, if there are two conditions to be tested, we can use the logical functions AND or OR depending on the situation, or we can use the other conditional functions to test even more ifs inside a single if., (i.e., one IF inside another IF statement) as follows:
Date Range =IF(A4<>””,TEXT(A4,”mmm d”) & IF(B4<> “”, “-” & TEXT(B4,”mmm d”), “”),””)
So we can see that the above formula returns an empty string if the start date is missing.
In case both the dates are missing, then also an empty string is returned.
Things to Remember
- We can even create a list of sequential dates using the ‘Fill Handle’ command. To do this, we can select the cell having a start date, and then drag it to the range of cells where we wish to fill. Click on ‘Home’ tab -> ‘Editing’ -> ‘Fill Series’ and then choose a date unit we wish to use.
- If we wish to calculate the duration or number of days between two dates, we can simply subtract the two dates using the ‘-‘ operator, and we will get the desired result.
Note: The format of cells: A2 and B2 is ‘Date,’ whereas that of cell C2 is ‘General’ as it calculates the number of days.
This has been a guide to Date Range in Excel. Here we discuss formulas to create date range in excel with different ways along with a downloadable excel template. You may learn more about excel from the following articles –