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.

**Table of Contents**Expand

## 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.

**Read More:** How to Use Formula for Past Due Date in Excel

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

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

**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**(**“ “**).

**Read More: **How to Use IF Formula for Date Range in Excel

## 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)`

**Read More:** How to Calculate Average If within Date Range in Excel

### 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.

**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.

## 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.

**Related Articles**

- How to Use SUMIFS to SUM Values in Date Range in Excel
- Use SUMIFS with Date Range and Multiple Criteria
- How to Calculate Average True Range in Excel
- Calculate Moving Range in Excel
- How to Use SUMIFS to SUM Values in Date Range in Excel
- Calculate Interquartile Range in Excel
- How to Calculate Percentage Range in Excel