Convert Excel Dates to Week Start Dates for a Pivot Table
Question details
Convert daily Excel dates to their corresponding week-start dates for pivot table grouping.

- 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.
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.
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.
In the source data, add a column headed Week Start next to the date column.
In B2, enter =A2-WEEKDAY(A2,1)+1. Replace A2 with the first date cell if your source dates are in a different column.
Drag the fill handle down the Week Start column, then select the results and choose Home > Number Format > Short Date or another date format.
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.

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. Open the workbook: Open your Excel workbook in WPS Spreadsheet and locate the column containing the daily dates.
- 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. 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. 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.

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.




