Calculate Average Date Durations Within a Date Range in Excel
Question details
Calculate the duration between two dates for each record and return the average duration only for rows within a specified date range.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The worksheet contains start dates, finish dates, and a date column used to determine which records qualify for the average.
- Observed behavior
- The required result is the mean number of days for qualifying records, excluding rows dated before the selected start date or after the selected end date.
Confirm that the start date, finish date, and criteria date columns contain real Excel dates rather than text. Identify the cells that hold the beginning and ending dates for the reporting period.
Use a helper duration column and AVERAGEIFS
Use this method when you want a widely compatible formula that averages durations based on an inclusive date range.
Calculate the number of days for every row first, then use AVERAGEIFS to average only durations whose related date is between the selected start and end dates.
In the first cell of a new Duration column, enter a subtraction formula such as =C2-B2, where B2 is the Start Date and C2 is the Finish Date. Press Enter and fill the formula down the column.
Enter the report start date and end date in two cells, such as H2 and H3. Format both cells as Date from Home > Number Format if necessary.
Select the result cell and enter =IFERROR(AVERAGEIFS(D2:D100,A2:A100,">="&H2,A2:A100,"<="&H3),""). Replace D2:D100 with the Duration range and A2:A100 with the date column used for qualification.
Use FILTER and AVERAGE in Excel 2021 or Microsoft 365
Use this option when your Excel version supports the FILTER function and you prefer not to use AVERAGEIFS.
Average Date Durations by Date Range in WPS Spreadsheets
WPS Spreadsheets lets you calculate row-by-row date durations and use AVERAGEIFS to return the average only for records in your selected reporting period. Its familiar spreadsheet interface makes it easy to open an existing workbook and apply the formula without rebuilding the sheet.
- 1. Open the workbook in WPS Spreadsheets: Open WPS Office, choose Spreadsheets, and select the Excel workbook containing the start date, finish date, and reporting date columns.
- 2. Add a Duration column: Insert a column beside the date data, select the first duration cell, and enter a formula such as =C2-B2. Drag the fill handle down to calculate all rows.
- 3. Enter the reporting dates: Type the period start date and end date into two separate worksheet cells, then select them and choose Home > Number Format > Date if they are not displayed as dates.
- 4. Apply the average formula: Select the result cell and enter =IFERROR(AVERAGEIFS(D2:D100,A2:A100,">="&H2,A2:A100,"<="&H3),""). Adjust the duration range, qualifying date range, and date input cells to match your worksheet.
Frequently Asked Questions
How do I calculate the number of days between two dates in Excel?
In a Duration column, subtract the start date from the finish date, such as =C2-B2. Format the result cell as General or Number to display the number of days.
Which date column should I use for the AVERAGEIFS criteria?
Use the date column that determines whether a record belongs in the reporting period. It may be the start date, finish date, completion date, or another event date, depending on your reporting rule.
Does AVERAGEIFS include the start and end dates in the range?
Yes, when you use criteria such as ">="&H2 and "<="&H3, records dated exactly on either boundary are included.
Why does my average date-duration formula return an error?
Check that the date cells are valid dates, the duration range contains numeric values, and at least one row meets both criteria. Wrap the formula in IFERROR if an empty result is acceptable when no rows qualify.
Can I average durations without creating a helper column?
In Excel 2021 or Microsoft 365, you can calculate within a dynamic-array formula, but a helper Duration column is easier to audit and works reliably with AVERAGEIFS across more spreadsheet versions.
