Group Dates by Sunday-to-Saturday Week and Average Durations in Excel
Question details
Group dates into Sunday-to-Saturday weeks and calculate the average duration for each week in Excel.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains dates and durations that need to be summarized by calendar week.
- Observed behavior
- The user needs weekly averages, but the PivotTable does not offer a direct Week grouping option.
Make sure the date and duration columns contain actual Excel date and numeric values rather than text. The examples assume dates are in column A, durations are in column B, and row 1 contains headers.
Create a Week-Start Column and Average Durations in a PivotTable
Use a Sunday date as the weekly grouping key, then summarize durations with a PivotTable.
Enter Week Start in C1. In C2, enter =A2-WEEKDAY(A2,1)+1, then copy the formula down for every data row.
Select column C and choose Home > Number Format > Short Date so each grouping key displays as a date.
Select the source data, including the Week Start column, and choose Insert > PivotTable. Choose where to place it and select OK.
In the PivotTable Fields pane, drag Week Start to Rows and the duration field to Values.
Open the duration field's menu in Values, select Value Field Settings, choose Average, and select OK. Use Number Format in the same dialog to display the result as a duration if needed.

Calculate Weekly Averages with AVERAGEIFS
Use formulas instead of a PivotTable when you want weekly averages in a worksheet range.
Calculate Sunday-to-Saturday Averages in WPS Spreadsheets
Use a week-start formula and AVERAGEIFS in WPS Spreadsheets to summarize durations by Sunday-to-Saturday week.
- 1. Open your spreadsheet: Open WPS Spreadsheets and select File > Open to choose your workbook. Place dates in column A and durations in column B.
- 2. Add the week-start formula: Enter Week Start in C1 and =A2-WEEKDAY(A2,1)+1 in C2. Copy the formula down column C.
- 3. List the weeks: Enter the Sunday week-start dates you want to summarize in column F, starting at F2.
- 4. Calculate the averages: Enter =AVERAGEIFS($B$2:$B$22,$C$2:$C$22,F2) in G2, adjust the ranges to match your data, and copy the formula down.

Frequently Asked Questions
Why use a week-start date instead of a week number?
A Sunday week-start date distinguishes weeks across years. A week number alone can combine records from different years when used as a grouping field.
How do I show the Saturday at the end of each week?
If the Sunday week-start date is in C2, enter =C2+6 in another column and format that cell as a date.
Why does my PivotTable show a sum instead of an average?
In the PivotTable Fields pane, open the duration field's menu under Values, select Value Field Settings, and choose Average.
How do I display an average duration longer than 24 hours?
Select the result cells and choose Home > Number Format > More Number Formats > Custom. Enter [h]:mm:ss to show elapsed hours without resetting at 24.
What if AVERAGEIFS is unavailable?
For a week-start date in F2, use =SUMIFS($B$2:$B$22,$C$2:$C$22,F2)/COUNTIF($C$2:$C$22,F2) when every matching duration cell contains a number. Adjust the ranges to fit your data.




