Function Problems

Convert Excel Dates to Week Start Dates for a Pivot Table

Khadija KhanKhadija Khan Nov 22, 2026 868 views

Question details

Convert daily Excel dates to their corresponding week-start dates for pivot table grouping.

Convert Excel Dates to Week Start Dates for a Pivot Table
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to group daily records by week in a pivot table, with Sunday as the first day of the week.
Observed behavior
Each daily date should produce the preceding Sunday or the same date if it is already Sunday, such as January 28 or February 4, 2018.
Before you start

Check that the source dates are recognized as Excel dates rather than text, and identify the column containing them. The formulas below assume the first date is in cell A2.

Solution 1Recommended

Calculate Sunday Week Starts and Group Them in a Pivot Table

Add a helper column that converts each daily date to its week's Sunday, then use that column in the pivot table.

1
Add a helper column

In the source data, add a column headed Week Start next to the date column.

2
Enter the Sunday formula

In B2, enter =A2-WEEKDAY(A2,1)+1. Replace A2 with the first date cell if your source dates are in a different column.

3
Fill and format the results

Drag the fill handle down the Week Start column, then select the results and choose Home > Number Format > Short Date or another date format.

4
Use the column in the pivot table

Create a pivot table from the full source range using Insert > PivotTable. Drag Week Start to Rows and add the fields you want to summarize to Values. For an existing pivot table, include the new column in PivotTable Analyze > Change Data Source, then refresh it.

Calculate Sunday Week Starts and Group Them in a Pivot Table
Use Monday instead: If your week starts on Monday, enter =A2-WEEKDAY(A2,2)+1 in the helper column instead.
Calculate weekly dates in WPS

Group Dates by Week in WPS Spreadsheet

Use the WEEKDAY formula in WPS Spreadsheet to create week-start dates, then add the helper column to a pivot table.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and locate the column containing the daily dates.
  2. 2. Calculate week starts: Add a Week Start column and enter =A2-WEEKDAY(A2,1)+1 in the first data row, adjusting A2 to match your date cell.
  3. 3. Fill and format the column: Drag the formula's fill handle down, then select the results and apply a date format from the Home tab.
  4. 4. Create the pivot table: Select the source data, including Week Start, and choose Insert > PivotTable. Add Week Start to the row fields and the desired measures to the value fields.
Calculate Sunday or Monday week starts with a familiar formulaGroup records by the helper column in a pivot tableOpen and save Microsoft Excel formats, including XLSXWork with a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

How do I make the week start on Monday instead of Sunday?

In the helper column, use =A2-WEEKDAY(A2,2)+1, replacing A2 with the cell containing the date.

Why does my week-start formula show a number instead of a date?

Excel stores dates as serial numbers. Select the formula cells and choose Home > Number Format > Short Date or another date format.

Why doesn't my new Week Start field appear in the existing pivot table?

The pivot table's source range may not include the new column. Select the pivot table, choose PivotTable Analyze > Change Data Source, expand the range, and refresh the pivot table.

What happens if the source date is already a Sunday?

The Sunday-start formula returns that same date. For example, February 4, 2018 returns February 4, 2018.