Hosne Ara
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, and year. Luckily, there are some inbuilt Excel functions available to calculate date ranges. So, now I will show you how to calculate a date range in Excel with 4 ideal methods.
Download Practice Workbook
You can download the practice Excel file we used to prepare this article.
4 Ideal Methods to Calculate Date Range in Excel
To illustrate the processes, here is a sample dataset. It shows 5 Product IDs in Column B. we need to find out the Invoice Date and Payment Date as a range of each product.
1. Use Arithmetic Formula to Calculate Date Range
You can create a date range very easily by using a simple arithmetic 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.
- First, write the formula in Cell D5.
=C5+7
- After that, the formula will give the following output.
- Then, write the below formula in Cell C6.
=D5+1
- Consequently, press Enter to get the result.
- Now, copy Cell D5 to Cell D6.
- Then, select both ranges and drag them down using the Fill Handle tool.
- Eventually, you will get the following result.
Note: If your start is the present day then use the TODAY function and it will generate this formula.
=TODAY()
Read More: How to Filter Date Range in Excel (5 Easy Methods)
2. Create Date Sequence with Date Range in Excel
Another easy way to create a date range in Excel is to use a date sequence. For instance, you just write the 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 steps to do the task.
- First, write the Invoice and Payment Dates of the first 2 products in the Cell range C5:D6.
- Then, select both ranges and drag them down.
- Finally, you will get the expected date ranges.
Read More: How to Use Formula for Past Due Date in Excel (3 Methods)
3. Insert TEXT Function to Calculate Date Range in Excel
If you have the first and the last date 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. The steps involved in this example are given below.
- In the beginning, type 1st product’s Invoice and Payment Dates in Cell C5 and Cell D5.
- Later, write the following formula in Cell D5.
=TEXT(C6,"d mmm yy") & "-" & TEXT(D6,"d mmm yy")
- Lastly, press Enter to get the following result.
- Next, let’s change the format of the concatenated date range by using this formula in Cell D6.
=TEXT(C6,"d mmm yy") & "-" & TEXT(D6,"d mmm yy")
- Therefore, the final output is as follows.
Similar Readings
- How to Do SUMIF Date Range Month in Excel (9 Ways)
- Excel SUMIF with a Date Range in Month & Year (4 Examples)
- How to Calculate Range for Grouped Data in Excel (3 Effective Methods)
- Excel VBA: Filter Date before Today (With Quick Steps)
- How to Find Max Date in Range with Criteria in Excel
4. Combine TEXT & IF Functions to Create a Date Range
In our previous method, we 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.
- In our first example, the Payment Date of the range is missing.
- Therefore, we will use the following formula.
=TEXT(C5,"mmm d")&IF(D5<>""," - "&TEXT(D5,"mmm d"),"")
- The output of the above-mentioned formula is as follows.
- In this case, both the start and end dates of the range are missing.
- Henceforth, we will use the following formula.
=IF(C6<>"",TEXT(C6,"mmmm d")&IF(D6<>""," - "&TEXT(D6,"mmm d"),""),"")
- In the end, you’ll get the output as Blank (“ “).
Read More: How to Use IF Formula for Date Range in Excel (6 Methods)
Calculate Interval 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 Mathematical Operation to Calculate Interval within Date Range
This is the easiest way to calculate the number of days between a date range. The steps involved in the process are given below.
- Firstly, calculate the difference between the dates in Cell C5 and Cell D5 with this formula.
=D5-C5
- As a result, the above formula will show the output as a number for the dates in the specified date range.
- Lastly, use the Autofill tool to get all the intervals.
Note: You can also apply the DAYS function to subtract the dates and get intervals. For this, use this formula based on the above dataset.
=DAYS(D5, C5)
Read More: How to Calculate Average If within Date Range in Excel (3 Ways)
2. Calculate Date Range Interval with DATEDIF Function
The use of the DATEDIF Function is another way to calculate the number of days or months or years between a given date range.
- Firstly, let us calculate the date range difference in Year with this formula.
=DATEDIF(C5,D5,"Y")
- Then, press Enter to get this output.
- Secondly, we will calculate the date difference in a Month. So, our formula is as follows.
=DATEDIF(C6,D6,"M")
- Lastly, we are going to calculate the difference in Days with this formula.
=DATEDIF(C7,D7,"d")
Read More: Excel Formula to Add Date Range (11 Quick Methods)
Conclusion
In this article, we have tried to explain 4 ideal methods of how to calculate date ranges in Excel. 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 or suggestions please let us know. Follow ExcelDemy for more articles like this.
Related Articles
- How to Use SUMIFS to SUM Values in Date Range in Excel
- Use SUMIFS with Date Range and Multiple Criteria (7 Quick Ways)
- How to Calculate Average True Range in Excel (with Easy Steps)
- Calculate Moving Range in Excel (4 Simple Methods)
- How to Use SUMIFS to SUM Values in Date Range in Excel
- Calculate Interquartile Range in Excel (2 Suitable Ways)
- How to Calculate Percentage Range in Excel (Step-by-Step Guide)
