How to Calculate Date Range in Excel (4 Ideal Methods)

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.

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. Using Arithmetic Formula to Calculate Date Range in Excel

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

2. Creating 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.

3. Inserting Excel TEXT Function to Calculate Date Range

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")

Here, the TEXT function converts values of Cells C5 and D5 to text in a specific number format. Then, the Ampersand (&) operator is used to get the date range value in the custom format (“mmm d”) in a single cell.

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.

4. Combining TEXT & IF Functions to Create a Date Range in Excel

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"),"")

In this formula, the TEXT function returns the value in a number format. Then, the formula 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 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 (“ “).

How to 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. Using 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)

2. Calculating Date Range Interval with DATEDIF Function in Excel

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.

Here, the DATEDIF function helps to calculate the number of years from Cells C5 and D5.

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

Download Practice Workbook

You can download the practice Excel file we used to prepare this article.

Calculate Date Range.xlsx

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.

Get FREE Advanced Excel Exercises with Solutions!