Function Problems

How to Group Dates into Custom Sunday-to-Saturday Weeks in Excel

Muhammad TalhaMuhammad Talha Nov 11, 2026 868 views

Question details

The user needs a method to assign dates from 2024 and 2025 into weekly groups that specifically run from Sunday to Saturday.

Group Dates into Custom Sunday-to-Saturday Weeks in Excel
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.
Before you start

Ensure your date column is formatted as actual dates in Excel, rather than text, to allow the formulas to accurately calculate the week ranges.

Solution 1Recommended

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.

1
Select the target cell

Click on the empty cell adjacent to your first date where you want the week group label to appear.

2
Enter the WEEKNUM formula

Type `=WEEKNUM(A2, 1)` (assuming your date is in cell A2). The '1' specifies that the week begins on Sunday.

3
Combine with the year

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'.

4
Apply to all rows

Drag the fill handle down from the bottom right corner of the cell to apply this formula to all dates in your dataset.

Use the WEEKNUM Formula for Sunday-to-Saturday Grouping
Formatting Tip: Using the TEXT function to add a leading zero to single-digit week numbers (e.g., W05 instead of W5) ensures your data sorts correctly in Pivot Tables.
Organize Data Efficiently

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. 1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the 2024 and 2025 dates.
  2. 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. 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. 4. Summarize the data: Go to the Insert tab and select PivotTable to easily summarize your data based on these newly created weekly groups.
Fully compatible with Microsoft Excel formulas and .xlsx date formats.Built-in WEEKNUM and lookup functions for quick data categorization.Lightweight and fast, handling large multi-year datasets smoothly.Free to use with a familiar, user-friendly interface.
microsoft office alternative - wps office

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")`.