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

Get FREE Advanced Excel Exercises with Solutions!

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.

Output of Data Range Calculation

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.

Dataset for Calculating Date Range


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

Inserting Formula to Calculate 1st Product’s Payment Date

  • After that, the formula will give the following output.

Output of Applying Arithmetic Formula

  • Then, write the below formula in Cell C6.
=D5+1

Inserting Formula to Get 2nd Product’s Invoice Date

  • Consequently, press Enter to get the result.

Invoice Date of 2nd Product

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

Final Result of Using Arithmetic Formula to Calculate Date Range

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.

Typing Start and End Dates

  • Finally, you will get the expected date ranges.

Final Output of Creating Date Sequence

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

Inserting Formula to Calculate Date Range

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.

Output of TEXT Function

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

Using TEXT Function to Calculate Date Range in Different Format

  • Therefore, the final output is as follows.

How to Calculate Date Range in Excel


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

Applying Formula to Calculate 1st Product’s Date Range

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.

Output of Using TEXT & IF Functions

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

Using TEXT & IF Functions for Calculating 2nd Product’s Date Range

  • In the end, you’ll get the output as Blank (“ “).

Final Output where Both Dates are Missing

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

Inserting Formula to Count Interval of Dates

  • As a result, the above formula will show the output as a number for the dates in the specified date range.

Result of Applying Mathematical Operation

  • Lastly, use the Autofill tool to get all the intervals.

Final Output on Intervals among Dates of All Products

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

Calculating Interval of Years with DATEDIF Function

  • Then, press Enter to get this output.

Result of Using DATEDIF Function

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

Calculating Interval of Months

  • Lastly, we are going to calculate the difference in Days with this formula.
=DATEDIF(C7,D7,"d")

Calculating Interval of Days with DATEDIF Function

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo