How to Group Dates into Custom Sunday-to-Saturday Weeks in Excel
Question details
The user needs a method to assign dates from 2024 and 2025 into weekly groups that specifically run from Sunday to Saturday.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Organizing and summarizing multi-year time-series data into custom weekly intervals for reporting or pivot table analysis.
- Observed behavior
- Requires a formula or workbook helper table structure to automatically categorize individual daily dates into their correct Sunday-to-Saturday week buckets.
Ensure your date column is formatted as actual dates in Excel, rather than text, to allow the formulas to accurately calculate the week ranges.
Use the WEEKNUM Formula for Sunday-to-Saturday Grouping
The WEEKNUM function is the most direct way to assign a week number to a date, with Sunday as the default start of the week.
By combining the YEAR and WEEKNUM functions, you can create unique weekly identifiers that distinguish between weeks in 2024 and 2025.
Click on the empty cell adjacent to your first date where you want the week group label to appear.
Type `=WEEKNUM(A2, 1)` (assuming your date is in cell A2). The '1' specifies that the week begins on Sunday.
To distinguish between 2024 and 2025, wrap the formula like this: `=YEAR(A2)&"-W"&TEXT(WEEKNUM(A2, 1),"00")`. This outputs a clean format like '2024-W05'.
Drag the fill handle down from the bottom right corner of the cell to apply this formula to all dates in your dataset.

Create a Lookup Table for Custom Date Ranges
A helper table is easier to maintain and customize if you need specific text labels for each Sunday-to-Saturday period.
Group Dates Easily with WPS Spreadsheet
WPS Spreadsheet provides full support for advanced date and time functions, including WEEKNUM and VLOOKUP, allowing you to organize your weekly data across multiple years effortlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the 2024 and 2025 dates.
- 2. Apply the grouping formula: In an empty column next to your dates, type `=WEEKNUM(A2, 1)` to extract the Sunday-to-Saturday week number.
- 3. Format the output label: Use `=YEAR(A2)&"-W"&TEXT(WEEKNUM(A2, 1),"00")` to create a clear, sortable weekly group label for multiple years.
- 4. Summarize the data: Go to the Insert tab and select PivotTable to easily summarize your data based on these newly created weekly groups.

Frequently Asked Questions
How do I change the week start day from Sunday to Monday?
In the WEEKNUM formula, change the return_type argument from 1 to 2. For example, `=WEEKNUM(A2, 2)` will calculate the week starting on Monday instead of Sunday.
Why does my WEEKNUM formula return a #VALUE! error?
This usually happens if the referenced cell contains text instead of a valid Excel date format. Select the cell, go to the Home tab, and change the number format to 'Short Date' to fix this.
Can I display the actual Sunday and Saturday dates instead of a week number?
Yes. To get the Sunday start date, use `=A2-WEEKDAY(A2)+1`. To get the Saturday end date, use `=A2-WEEKDAY(A2)+7`. You can combine them using `=TEXT(A2-WEEKDAY(A2)+1, "mm/dd") & " - " & TEXT(A2-WEEKDAY(A2)+7, "mm/dd")`.




