Pivot Table Issues

Group Dates by Sunday-to-Saturday Week and Average Durations in Excel

Emma BrownEmma Brown Nov 22, 2026 868 views

Question details

Group dates into Sunday-to-Saturday weeks and calculate the average duration for each week in Excel.

Group Dates by Sunday-to-Saturday Week and Average Durations 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.
Before you start

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.

Solution 1Recommended

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.

1
Add the week-start formula

Enter Week Start in C1. In C2, enter =A2-WEEKDAY(A2,1)+1, then copy the formula down for every data row.

2
Format the week-start dates

Select column C and choose Home > Number Format > Short Date so each grouping key displays as a date.

3
Insert a PivotTable

Select the source data, including the Week Start column, and choose Insert > PivotTable. Choose where to place it and select OK.

4
Arrange the fields

In the PivotTable Fields pane, drag Week Start to Rows and the duration field to Values.

5
Change the calculation to Average

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.

Create a Week-Start Column and Average Durations in a PivotTable
Weeks across years: The Sunday date identifies each week without combining matching week numbers from different years. A week that crosses New Year's Day remains a single Sunday-to-Saturday group.
Calculate weekly averages in WPS

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. 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. 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. 3. List the weeks: Enter the Sunday week-start dates you want to summarize in column F, starting at F2.
  4. 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.
Group dates by the Sunday that starts each weekCalculate average durations with worksheet formulasOpen and save Microsoft Excel XLSX filesWork with a familiar spreadsheet interface
microsoft office alternative - wps office

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.