In this article, I will discuss how to calculate a date range in excel. Usually, calculating the date range is quite more complex than other operations in excel. These calculations are a bit different from other ones associated with values, numbers, etc. A date contains different parts like day, month, year. Luckily, there are some inbuilt excel functions available to calculate date ranges. So, now I will show you creating date ranges and their calculation methods.
Download Excel Workbook
You can download the practice Excel file that we have used to prepare this article.
Four Frequently Used Methods to Calculate Date Range
1. Use Simple Arithmetic Calculation to Add a Date Range
You can create a date range very easily by using a simple formula. For example, you can just add the desired date difference to adjacent cells. This is one of the easiest ways to create date ranges in excel. In our given example, we have a date in Cell B5, so here are the following steps to create the range:
- Write the formula in Cell C5.
After writing the formula the output in column C5 is:
- Then write the below formula in Cell B6.
Consequently, we will get the following result:
- Now, copy Cell C5 to Cell C6.
- Then, select both the ranges and drag them down using the Fill Handle (+). Eventually, you will get the following result.
2. Create a Date Sequence with a Date Range in Excel
Another easy way to create a range in excel is to use a date sequence. For instance, you just write to ranges and Excel will do the rest of the work. This is an amazing way to create ranges following a particular difference. Here are the following steps:
- Firstly, write the first and last date of the two ranges.
- Then, select both the ranges and drag them down to get the expected date ranges.
3. Insert Excel TEXT Function to Create a Date Range
If you have first and the last date a date range you can create the date range and show it in a single cell. Fortunately, excel has inbuilt functions to do the task. In the following example, we will use the TEXT Function. Steps involved in this example are:
📌 Steps :
- Type first and date in Cell B5 and Cell C5. Later that, write the following formula:
=TEXT(B5,"mmm d") & "-" & TEXT(C5,"mmm d")
Here, the TEXT function converts a value to text in a specific number format. In this formula ampersand (&) operator is also used to get the date range value in the custom format (“mmm d”) in a single cell. Lastly, we get the result in Cell D5, which is:
- We can also change the format of the concatenated date range by using following the formula:
=TEXT(B6,"d mmm yy") & "-" & TEXT(C6,"d mmm yy")
- The final output is:
- How to Do SUMIF Date Range Month in Excel (9 Ways)
- Excel SUMIF with a Date Range in Month & Year (4 Examples)
4. Create a Date Range If Start or End Date is Missing
In our previous method, we have put the date range in a single cell when both the dates of the range were available. Conversely, there are situations when the start or end date of the range is not available. In such situations, we can use a combination of the IF Function and TEXT function. Here are the following steps involved:
📌 Steps :
- In our first example, End date of the range is missing. We will use the following formula:
=TEXT(B5,"mmm d")&IF(C5<>""," - "&TEXT(C5,"mmm d"),"")
IF Function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
- The output of the above-mentioned formula is:
- In case both start and end of the range is missing we will use the following formula:
=IF(B6<>"",TEXT(B6,"mmmm d")&IF(C6<>""," - "&TEXT(C6,"mmm d"),""),"")
- In the end, you’ll get the output as “ “ blank.
Calculate Number of Days within a Date Range in Excel
There are some easy ways to calculate the difference of a date range in excel. Besides, there are functions available to calculate the difference. Let us discuss two direct and easy methods to do the task.
1. Use Formula to Calculate Date Range Difference
This is the easiest way to calculate the number of days between a date range. Steps involved in the process are:
📌 Steps :
To calculate the difference between the dates in Cell B5 and Cell C5, the formula is:
- The above formula will show the result as the number of dates in the specified date range. So, the output is:
2. Calculate Date Range Difference Using DATEDIF Function
Use of the DATEDIF Function is another way to calculate the number of days or months or years between a given date range.
📌 Step 1:
At first, let us calculate the date range difference in year:
The formula used here is:
And, the output is:
📌 Step 2:
Secondly, we will calculate the date difference in a month. So, our formula is:
📌 Step 3:
Thirdly, we are going to calculate the difference in days and the formula is:
Finally, the results are:
In this article, we have tried to explain easy and direct ways to create date ranges. Also, we have discussed calculating differences between date ranges. There are more complex ways to do these tasks in excel, but the methods mentioned above take less time. If you have any comments and suggestions please let us know.