Excel Formula to Add Date Range (11 Quick Methods)

Date Range in Excel helps us to do a calculation on date values. In this article, we are going to know more about the Excel Formula for the date range with relatable explanations and examples.


Practice Workbook

Download the following workbook and exercise.


11 Quick Methods to Add Date Range in Excel

1. Excel Formula for Basic Date Range

Assuming we have a dataset (B4:D9) of different projects. The starting date of the first project is in Cell C5. This project will end after two days and the next project will also start within two days of its ending. Let’s see what will happen when we add a number (2) for creating a date range.

Excel Formula for Basic Date Range

STEPS:

  • First, select Cell D5.
  • Next type the formula:
=C5+2

Excel Formula for Basic Date Range

  • Now hit Enter to see the result

Excel Formula for Basic Date Range

  • Then select Cell C6.
  • Type the formula:
=D5+2

Excel Formula for Basic Date Range

  • Press Enter and see the result.

  • After that, copy Cell D5 and paste it into Cell D6.

➥ The cell reference changes here for the copy-paste.

  • Finally, select C6:D6 and use the Fill Handle to autofill the cells to see the result.


2. Create Date Sequence Automatically with AutoFill

Excel takes dates as numerical numbers. So we can use this advantage to create a date sequence like a number sequence. Let’s say we have a dataset (B4:D9) of different projects. We are going to make a date sequence like the first two projects for the next two.

Create Date Sequence Automatically with AutoFill

STEPS:

  • Select the cell range C5:D6 at first.

  • Now use the Fill Handle to drag down the cells below and see the result.


3. Excel Formula for Horizontal Date Range

With the help of the TEXT, DATE, and COLUMNS functions, we can easily create a date range. From the below dataset (C4:E5), we are going to create a horizontal date range of 7 days.

Excel Formula for Horizontal Date Range

STEPS:

  • First, select Cell C5.
  • Type the formula:
=TEXT(DATE(2010, 11, 1)+(COLUMNS($C:C)-1)*7, "mm/dd/yy")&"-"&TEXT(DATE(2010, 11, 1)+(COLUMNS($C:C)-1)*7+6, "mm/dd/yy")

  • Now hit Enter and use Fill Handle to the right side to see the result.

Formula Breakdown

DATE(2010, 11, 1)

This will indicate the starting date of the date range.

COLUMNS($C:C)-1)*7

This will create a number determined by the current column which indicates the number of dates between the start dates of the date range.

TEXT(DATE(2010, 11, 1)+(COLUMNS($C:C)-1)*7, “mm/dd/yy”)

This will return the date 11/01/10.

TEXT(DATE(2010, 11, 1)+(COLUMNS($C:C)-1)*7+6, “mm/dd/yy”)

This will return the date 11/07/10.


4. Excel Formula for Vertical Date Range

Here we have a dataset (B4:C8) of the projects. We are going to create a date range excel formula which will work for the vertical cell range.

Excel Formula for Vertical Date Range

STEPS:

  • First, select Cell C5.
  • Type the formula:
=TEXT(DATE(2011, 11, 10)+(ROW(1:1)-1)*7, "mm/dd/yy")&"-"&TEXT(DATE(2011, 11, 10)+(ROW(1:1)-1)*7+6, "mm/dd/yy")

  • Next hit Enter and use the Fill Handle to see the rest of the results.

Formula Breakdown

DATE(2011, 11, 10)

This will indicate the starting date of the date range.

ROW(1:1)-1)*7

This will create a number determined by the current row which indicates the number of dates between the start dates of the date range.

TEXT(DATE(2011, 11, 10)+(ROW(1:1)-1)*7, “mm/dd/yy”)

This will return the date 11/10/11.

TEXT(DATE(2011, 11, 10)+(ROW(1:1)-1)*7+6, “mm/dd/yy”)

This will return the date 11/16/11.


5. Insert TEXT Function to Add Date Range in Excel

To convert a numeric value into a text string from a worksheet, we use the Excel TEXT function. Let’s say we have a dataset (B4:D8). We are going to make a date range in Cell F5 by joining two date cells.

Insert TEXT Function to Add Date Range in Excel

STEPS:

  • In the beginning, select Cell F5.
  • Then type the formula:
=TEXT(C5,"mmm d")&" - "&TEXT(D5,"mmm d")

  • Now use the Fill Handle to drag down the next cells and see the result.


6. Excel Formula For Date Range If End Date Is Missing

Sometimes we see that the ending date is missing from the dataset like below (B4:D8). To create a date range for that, we need to insert a formula with the TEXT function.

Excel Formula For Date Range If End Date Is Missing

STEPS:

  • Select Cell F5.
  • Next type the formula:
=TEXT(C5,"mmm d")&IF(D5<>""," - "&TEXT(D5,"mmm d")," -")

  • At last, hit Enter and use the Fill handle tool. We can see the result.


Similar Readings:


7. Excel Formula For Date Range If Start Date Is Missing

Like the above example, it can be possible that the starting date is missing in the dataset (B4:D8). To create a date range for this case we should follow the below steps. Here we can see the result as ‘N/A’.

Excel Formula For Date Range If Start Date Is Missing

STEPS:

  • First, select Cell F5.
  • Now write down the formula:
=IF(C5<>"",TEXT(C5,"mmmm d")&IF(D5<>""," - "&TEXT(D5,"mmm d"),""),"N/A")

  • Next press Enter and use the Fill Handle tool to the below cells.


8. Use of SUMIFS Function to Add Date Range

In the dataset (B4:C8), we can see the amount of sold products on a date basis. Now we are going to calculate the total units (Cell E8) in a required date range (E5:F5).

Use of SUMIFS Function to Add Date Range

STEPS:

  • Select Cell E8 at first.
  • Next write down the formula:
=SUMIFS($C$5:$C$8, $B$5:$B$8, ">=" & $E$5, $B$5:$B$8, "<=" & $F$5)

  • At last, press Enter and see the result.

Formula Breakdown

$C$5:$C$8

This will return the sum range of units

$B$5:$B$8

This will check the start date.

“>=” & $E$5

This will return a value that is equal to or greater than the start date in Cell E5.

$B$5:$B$8

This will check for the end date.

  “<=” & $F$5

This will return a value that is equal to or less than the end date in Cell F5.

Read more: How to Use SUMIFS with Date Range and Multiple Criteria


9. Excel COUNTIFS Function to Add Date Range

We have a dataset (B4:C8) of the amount of the products with their order dates. Now we are going to find out the number of orders on a required date range (E5:F5).

Excel COUNTIFS Function to Add Date Range

STEPS:

  • Select Cell E8.
  • Type the formula:
=COUNTIFS($B$5:$B$8,">=" & $E$5, $B$5:$B$8, "<=" & $F$5)

  • Press Enter and we see the order amount.

Formula Breakdown

$B$5:$B$8

This will check the start date.

“>=” & $E$5

This will return a value that is equal to or greater than the start date in Cell E5.

$B$5:$B$8

This will check for the end date.

  “<=” & $F$5

This will return a value that is equal to or less than the end date in Cell F5.


10. Excel Formula with EDATE Function for Date Range

Excel EDATE function is a date math function. This function helps to get the same date of the past or future month. In the dataset (B4:D8), we have the starting dates. We are going to find out which would be the same dates after three months.

Excel Formula with EDATE Function for Date Range

STEPS:

  • First, select Cell D5.
  • Type the formula:
=EDATE(C5,3)

  • Now hit Enter and use the Fill Handle tool for the results.


11. Excel DATEDIF Function to Find Difference in Date Range

If we want to find out the total year difference between the date range, we use the Excel DATEDIF function. Here we have a dataset (B4:E8). We are going to find out the difference of years between the start date and end date.

Excel DATEDIF Function to Find Difference in Date Range

STEPS:

  • Select Cell E5 at first.
  • Now write down the formula:
=DATEDIF(C5,D5,"y")

  • In the end, press Enter and use the Fill Handle to see the result.


Conclusion

These are the easiest formulas for the date range in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.


Related Readings

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo