Excel Pivot Table Dates: How to Group and Sort by Week

Charts & pivot tables · Updated November 19, 2026

To sort PivotTable dates by seven-day periods in Excel for Microsoft 365 on Windows, right-click a date in the PivotTable, choose Group, select Days, enter 7 for the number of days, and set Starting at to the date you want each period to begin. For Monday-to-Sunday calendar weeks, a source-data helper column containing each date’s Monday is often the more reliable choice.

What “by week” means in a PivotTable

A week can mean a repeating seven-day block or a calendar week with a specific first day. Those are not always the same. If Excel groups every seven days starting from the first date in the data, a period might run Wednesday through Tuesday. That is a valid seven-day grouping, but it will not match a Monday-start business calendar.

Before grouping, decide what the report needs. A weekly sales total may use Monday through Sunday; a roster may use Sunday through Saturday; and a rolling analysis may need consecutive seven-day windows anchored to a particular date. The boundary matters because transactions near the start or end of a week will be counted in different periods depending on that choice.

Also distinguish a week number from a week-start date. Week numbers repeat every year: week 1 appears in 2024, 2025, and every other year. A date such as Monday, March 4, 2024, identifies a particular week unambiguously, so it is usually a better PivotTable row label.

Group dates into seven-day periods

Use this method when any continuous seven-day intervals are acceptable, or when you can set an appropriate starting boundary. The source date field must contain actual Excel dates rather than text that merely looks like dates. Select one date cell in the PivotTable, not a whole range of cells.

  1. Click a date label in the PivotTable. On the ribbon, open PivotTable Analyze > Group Field. You can also right-click the date and choose Group.
  2. In the Grouping dialog, select Days. Clear other selected units if you want only seven-day groups rather than a combination of groups.
  3. Enter 7 in Number of days. Set Starting at to the first day of the week you want. If necessary, set Ending at to cover the final date in your source data.
  4. Choose OK. Excel replaces the individual dates in the row or column field with date ranges. Add a numeric field, such as Sales, to the Values area to summarize each period.

For example, if the earliest transaction is Wednesday, March 6, 2024, and the report needs Monday weeks, set the starting boundary to Monday, March 4, 2024. The first group can then represent the week beginning March 4, rather than a Wednesday-to-Tuesday block. Check the displayed labels against a few source records; the group label represents the interval boundary, not necessarily a date on which a transaction occurred.

To order the groups chronologically, right-click a date-group label and choose Sort > Sort Oldest to Newest. The reverse option, Sort Newest to Oldest, puts the latest week first. If the dates look like text or sort alphabetically, verify that the PivotTable field is based on date values and not text labels.

Use a helper column for Monday-start weeks

A helper column is a dependable option when the report specifically requires Monday-to-Sunday weeks, when the Group command is unavailable, or when you want the week key visible in the source data. It calculates the Monday belonging to each transaction date. Grouping by that calculated date makes all records from the same Monday-to-Sunday week share one PivotTable label.

With transaction dates in A2:A20, enter this in B2 and fill it down. If A2 contains March 6, 2024, the result is March 4, 2024; format column B as a date if Excel displays a serial number.

=A2-WEEKDAY(A2,2)+1

The second argument, 2, makes Monday day 1 and Sunday day 7. Add the helper column to the PivotTable source, refresh the PivotTable, and place the new field—perhaps named Week starting—in Rows. Put the measure to summarize in Values. Because the helper is a real date, Excel can sort it chronologically and display it in a date format.

If the source is an Excel Table, add the helper as a table column so the formula can fill into new rows automatically. Refresh the PivotTable after adding records or changing dates. If the PivotTable source is a fixed cell range, make sure new rows are included in its source before refreshing; otherwise the new transactions will not appear.

Choose the right method for the report

Built-in grouping is quick and keeps the source table unchanged. It suits exploratory summaries and reports where seven-day intervals anchored at a chosen date are sufficient. A helper field takes a little more setup but makes the week definition explicit in the data and easier to reuse across PivotTables.

  • Choose Days = 7 for compact, built-in grouping when the start boundary is correct and the grouping command is available.
  • Choose a week-start helper for consistent Monday or Sunday weeks, reusable reports, or environments where PivotTable grouping is restricted.
  • Use a separate year and week number only when a week-number report is specifically required. Include the year; week number alone can combine different years.

For Sunday-start weeks, the same idea applies with Sunday as the first day: calculate the Sunday on or before each source date, then use that helper as the PivotTable row field. Whichever convention you use, document it in the report title or field name. “Week starting Monday” is clearer than a bare “Week” label when readers may assume different calendars.

Sort, format, and refresh the weekly report

Once the weekly field is in Rows, place the value you want to analyze in Values. Excel may default to Sum for numeric data and Count for text or certain mixed fields; open Value Field Settings if you need a different calculation, such as Average or Count. Sorting the row labels orders weeks by date, while sorting by a value ranks weeks by that measure—these are different tasks.

For a readable report, format the week-start field with a date format that makes the boundary clear, such as a month-day-year format. You can rename the row field to “Week beginning” and the Values heading to something meaningful. Avoid replacing the date key with text such as “Week 1” unless the year is also shown and the label sorts correctly; text labels can sort alphabetically rather than in calendar order.

After adding or changing source records, right-click the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. Refresh updates the summary from its current source; it does not automatically fix a source range that excludes newly appended rows. Excel Tables help avoid that range problem because the table expands as records are added.

Common grouping problems and fixes

If Group is disabled or Excel reports that the selection cannot be grouped, check the source date column first. A single blank, text value, or error in a date field can prevent grouping. Correct the source values, refresh the PivotTable, and try again. Text dates often need conversion to real dates; changing their number format alone does not convert text into date values.

Other common causes include selecting more than one PivotTable item, using a PivotTable based on the Data Model or an OLAP source, or working in a version or environment with limited grouping controls. In those cases, a calculated week-start field in the source is a practical alternative. In Excel for the web, if the date-grouping command is not available in the PivotTable interface, create or maintain the helper field in the source and use that field in the PivotTable. Excel for Microsoft 365 on Mac offers similar date grouping where supported; use the contextual right-click menu or the PivotTable ribbon, and use Control-click if your mouse has no right button.

If a week is split unexpectedly, confirm the grouping’s starting boundary and the intended first day of the week. If records contain date-and-time values, check that the time portion is not interfering with the desired grouping; a source helper that returns a consistent date key can make the intended week explicit. Finally, refresh after correcting source data. A PivotTable can continue to show cached results until it is refreshed.

Week numbers, ISO weeks, and year boundaries

Some reports need labels such as “2024-W10” rather than a week-start date. A week number by itself is ambiguous, and even pairing a calendar year with a week number can be misleading when the organization follows ISO week dates. ISO weeks begin on Monday, and the ISO week-year can differ from the calendar year near New Year’s Day. For example, a date in early January can belong to the final ISO week of the previous ISO year.

For most PivotTables, using the actual Monday date as the row field avoids that ambiguity and sorts naturally across years. If a formal ISO-week report is required, calculate and include both the ISO week number and the corresponding ISO week-year in the source, or use an established calendar table. Do not group by week number alone: records from the same numbered week in different years would be combined into one total.

Frequently Asked Questions

Can I group PivotTable dates by weeks automatically?
Yes. Select a date in the PivotTable, open the Grouping dialog, choose Days, and set Number of days to 7. Set the starting boundary deliberately, because Excel’s seven-day groups follow that anchor.

How do I make weeks start on Monday?
Set the grouping start boundary to a Monday that covers the source dates, or add a helper column that returns the Monday for each date and use that field in Rows. The helper method is usually easier to reuse.

Why is the Group option greyed out?
Look for blanks, text dates, errors, or multiple selected items in the date field. Grouping may also be restricted for a Data Model or OLAP PivotTable. Clean the source or use a week-start helper field.

Will grouping by seven days create ISO weeks?
Not by itself. Seven-day grouping creates intervals based on its starting boundary. ISO week numbering has specific Monday-start and year-boundary rules, so use an ISO week-year as well as the week number when that convention is required.

How do I sort the latest week to the top?
Right-click a week label and choose Sort > Sort Newest to Oldest. If the field contains text labels rather than dates, use a real date key such as the week-start date for chronological sorting.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.