How to Calculate Date Range in Excel

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:

๐Ÿ“Œ Steps:

  • Write the formula in Cell C5.
=B5+7

Use Simple Arithmetic Calculation to Add a Date Range

After writing the formula the output in column C5 is:

Use Simple Arithmetic Calculation to Add a Date Range

  • Then write the below formula in Cell B6.
=C5+1

Use Simple Arithmetic Calculation to Add a Date Range

Consequently, we will get the following result:

Use Simple Arithmetic Calculation to Add a Date Range

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

๐Ÿ“Œ Steps:

  • Firstly, write the first and last date of the two ranges.

Create a Date Sequence with a Date Range in Excel

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

Insert Excel TEXT Function to Create a Date Range

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:

Insert Excel TEXT Function to Create a Date Range

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

Insert Excel TEXT Function to Create a Date Range

  • The final output is:


Similar Readings:


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

Create a Date Range If Start or End Date is Missing

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:

Create a Date Range If Start or End Date is Missing

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

Create a Date Range If Start or End Date Is Missing

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

=C5-B5

Use Formula to Calculate Date Range Difference

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

=DATEDIF(B5,C5,"Y")

Calculate Date Range Difference Using DATEDIF Function

And, the output is:

Calculate Date Range Difference Using DATEDIF Function

๐Ÿ“Œ Step 2:

Secondly, we will calculate the date difference in a month. So, our formula is:

=DATEDIF(B6,C6,"M")

Calculate Date Range Difference Using DATEDIF Function

๐Ÿ“Œ Step 3:

Thirdly, we are going to calculate the difference in days and the formula is:

=DATEDIF(B7,C7,"d")

Calculate Date Range Difference Using DATEDIF Function

Finally, the results are:


Conclusion

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.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo