Method 1 – Using Arithmetic Formula to Calculate Date Range in Excel
- Enter the formula in Cell D5 and press Enter.
=C5+7
- The formula will give the following output.
- Enter the following formula in Cell C6.
=D5+1
- Press Enter to get the result.
- Copy Cell D5 to Cell D6.
- Select both ranges and drag them down using the Fill Handle tool.
- 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
Method 2 – Creating Date Sequence with Date Range in Excel
- Enter the Invoice and Payment Dates of the first 2 products in the Cell range C5:D6.
- Select both ranges and drag them down.
- You will get the sequential date ranges.
Read More: How to Use Formula for Past Due Date in Excel
Method 3 – Inserting Excel TEXT Function to Calculate Date Range
- Enter the 1st product’s Invoice and Payment Dates in Cell C5 and Cell D5.
- Enter the following formula in Cell D5.
=TEXT(C6,"d mmm yy") & "-" & TEXT(D6,"d mmm yy")
The TEXT function converts values of Cells C5 and D5 to text in a specific number format. The Ampersand (&) operator is used to get the date range value in the custom format (“mmm d”) in a single cell.
- Press Enter to get the following result.
- Change the format of the concatenated date range by using the following formula in Cell D6.
=TEXT(C6,"d mmm yy") & "-" & TEXT(D6,"d mmm yy")
- The final output is as follows.
Method 4 – Combining TEXT & IF Functions to Create a Date Range in Excel
- The Payment Date of the range is missing.
- Enter the following formula.
=TEXT(C5,"mmm d")&IF(D5<>""," - "&TEXT(D5,"mmm d"),"")
The TEXT function returns the value in a number format. The formula checks whether a condition is met and returns one value if TRUE and another value if FALSE.
- The output result will be as shown below.
- Both the start and end dates of the range are missing.
- Enter the following formula.
=IF(C6<>"",TEXT(C6,"mmmm d")&IF(D6<>""," - "&TEXT(D6,"mmm d"),""),"")
- 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
Method 1 – Using Mathematical Operation to Calculate Interval within Date Range
- Calculate the difference between the dates in Cell C5 and Cell D5 with the formula shown in the image below.
=D5-C5
- It will give the output as a number for the dates in the specified date range.
- 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 the following formula based on the above dataset.
=DAYS(D5, C5)
Read More: How to Calculate Average If within Date Range in Excel
Method 2 – Calculating Date Range Interval with DATEDIF Function in Excel
- Calculate the date range difference in Year with the formula shown in the following image.
=DATEDIF(C5,D5,"Y")
- Press Enter to get the output.
The DATEDIF function helps to calculate the number of years from Cells C5 and D5.
- Calculate the date difference in a Month. Enter the following formula.
=DATEDIF(C6,D6,"M")
- Calculate the difference in Days with the formula shown in the following image.
=DATEDIF(C7,D7,"d")
Read More: Excel Formula to Add Date Range
Download Practice Workbook
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
