Nuraida Kashmin
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.
STEPS:
- First, select Cell D5.
- Next type the formula:
=C5+2
- Now hit Enter to see the result
- Then select Cell C6.
- Type the formula:
=D5+2
- 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.
Read More: How to Use IF Formula for Date Range in Excel (6 Methods)
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.
STEPS:
- Select the cell range C5:D6 at first.
- Now use the Fill Handle to drag down the cells below and see the result.
Read More: Excel SUMIF with a Date Range in Month & Year (4 Examples)
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.
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.
Read More: VLOOKUP Date Range and Return Value in Excel (4 Suitable Methods)
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.
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.
Read More: How to Calculate Date Range in Excel
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.
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.
Read More: How to Use SUMIFS to SUM Values in Date Range in Excel
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.
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.
Read More: How to Filter Date Range in Pivot Table with Excel VBA
Similar Readings
- SUMIF between Two Dates and with Another Criteria (7 Ways)
- How to Calculate Average If within Date Range in Excel (3 Ways)
- Excel VBA: Filter Date before Today (With Quick Steps)
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’.
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.
Read More: How to Filter Date Range in Excel (5 Easy Methods)
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).
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).
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.
Read More: How to Do SUMIF Date Range Month in Excel (9 Ways)
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.
STEPS:
- First, select Cell D5.
- Type the formula:
=EDATE(C5,3)
- Now hit Enter and use the Fill Handle tool for the results.
Read More: How to Use Formula for Past Due Date in Excel (3 Methods)
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.
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.
Read More: How to Use Pivot Table to Filter Date Range in Excel (5 Ways)
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.
