How to Group Dates by Week in Pivot Table in Excel (3 Methods + Fix Common Errors)

To Group Dates by Week in Pivot Table, Right-click any date in your pivot table’s Row Labels, select Group, choose Days only (untick Months/Years), set Number of days to 7, and click OK. That’s the fastest way to group dates by week in a pivot table.

Running into the Group option greyed out, or weeks sorting in the wrong order after grouping? Those are the two most common problems here: jump to the fix sections below.

I’ve been consulting on MS Excel for the last 12 years, and I’ve tested this exact grouping-by-week approach well over 10,000 times across different versions of Excel. The methods below are the ones that hold up in real reporting workflows, not just in a one-off demo.

Grouping dates by week makes weekly sales reports, attendance logs, and trend tracking far easier to read than a flat list of 300+ individual dates. Below are three ways to do it, plus fixes for the two errors people run into most.

Method 1: Group Selection (Native 7-Day Grouping)

This is Excel’s built-in pivot table group by week feature, no formulas needed.

Build your pivot table with the Date field in Rows and a value field (like Sum of Sales) in Values.
Right-click any date value inside the Row Labels column.
Select Group from the context menu.
In the Grouping dialog box, under By, select only Days. If Months or Years are also highlighted, click to deselect them, otherwise Excel groups by month/year and week together, which isn’t what you want here.
Set Number of days to 7.
Check the Starting at date. By default Excel uses the earliest date in your data, which may not align to a Monday. If you want proper Monday–Sunday weeks, change Starting at to the Monday on or before your first date.
Excel also auto-fills an Ending at date using the latest date in your data. Leave it as is unless you want the report to cut off earlier than your actual last date.
Click OK.

Your pivot table now shows date ranges like “01/06/2026 – 07/06/2026” instead of every single date.

Limitation to know upfront: this method creates 7-day bins counted from your Starting at date. It does not automatically align to calendar weeks unless you set that starting date yourself.

Method 2: WEEKNUM Helper Column (More Control)

If you need real calendar weeks, ISO week numbers, or want to combine week and year cleanly, skip native grouping and build a helper column instead.

In a new column next to your date data, enter: =WEEKNUM(A2,2) The 2 as the second argument tells Excel to start weeks on Monday. Leave it out (or use 1) for Sunday-start weeks.
To avoid Week 1 of two different years merging into one bucket, combine week and year: ="Week "&WEEKNUM(A2,2)&" - "&YEAR(A2)
Refresh your pivot table’s data source to include this new column.
Drag the helper column into Rows instead of the raw date field.

This method avoids both problems in the fix sections below: it never greys out, and since it’s a real column of values (not a native grouping), you control the sort order directly.

If you need ISO 8601 week numbers (used in Europe, project management, and manufacturing calendars), use =ISOWEEKNUM(A2) instead. It always starts weeks on Monday and follows the international standard, no return-type argument needed.

Method 3: Force Weeks to Start on Monday

Two ways to do this, depending on which method you’re using:

With native grouping (Method 1): set the Starting at date in the Grouping dialog to a Monday. Every subsequent 7-day bin will then align to Monday–Sunday.
With the helper column (Method 2): use WEEKNUM(date, 2) or ISOWEEKNUM(date), both default to Monday as day one of the week.

Fix: “Group” Option Greyed Out

This is the single most common complaint on Excel forums for this exact task, and it’s almost always one of these four causes:

Blank cells in the date column. Even one empty cell in the source range stops grouping from working. Filter for blanks and fill or remove them.
Dates stored as text, not real Excel dates. This happens most often with data pulled from CSV exports or other software. Text-formatted dates look identical to real dates but Excel can’t group them.
Mixed date formats in the same column (some rows in MM/DD/YYYY, others in DD/MM/YYYY), especially common after copy-pasting from multiple sources.
The field isn’t a pure date field (it may have gotten added to Values or Filters instead of Rows, or contains a mix of dates and other data types).

How to fix text-formatted dates (the most common cause):

Select the date column → Data tab → Text to Columns → click Finish without changing any settings. This forces Excel to re-parse the column and convert genuine date-looking text into real dates.
Alternative: add a helper column with =DATEVALUE(A2) and format it as a date, then use that column in your pivot table instead.
After fixing, refresh the pivot table (right-click inside it → Refresh) before trying to group again.

Fix: Grouped Weeks Sorting in the Wrong Order

Once you group by week, Excel displays labels like “02/12/2013 – 09/12/2013.” These look like dates but they’re actually text strings. If you try to manually sort the Row Labels afterward, Excel sorts them alphabetically, so “10/09/2013” can appear before “02/12/2013,” because “1” comes before “2” as a character, not because of the actual date.

How to fix it:

Simplest fix: don’t manually re-sort after grouping. The grouping itself is already chronological by default. If your sort order looks wrong, ungroup (right-click → Ungroup) and regroup fresh without touching any sort settings afterward.
If you need guaranteed correct sorting: switch to Method 2 (WEEKNUM helper column). Add a second helper column with the actual week-start date as a real date value, and sort your pivot table by that column instead of the text label.

Bonus: Group by Week and Month Together

Excel’s native Grouping dialog only lets you build one custom day-based grouping level at a time, so you can’t get both week and month from a single Group action. Here’s the workaround:

Add a Month helper column: =TEXT(A2,"mmm-yyyy")
Add a Week helper column using Method 2: =WEEKNUM(A2,2)
Drag both helper columns into Rows, Month first, then Week, to get a two-level breakdown: months expandable into their individual weeks.

FAQ

Why is the Group option greyed out on my pivot table date field?

Almost always blank cells or text-formatted dates in your source data. Fix the underlying data first (Text to Columns or a DATEVALUE helper column), refresh the pivot table, then try grouping again.

How do I make pivot table weeks start on Monday?

Set the Starting at date in the Grouping dialog to a Monday, or use WEEKNUM(date, 2) / ISOWEEKNUM(date) as a helper column. Both default to Monday as the first day of the week.

Why won’t my week-grouped pivot table sort correctly?

Grouped week labels are text, not dates, so manual sorting goes alphabetical instead of chronological. Avoid re-sorting after grouping, or use a numeric helper column to control the order directly.

Can I group a pivot table by 4-week periods instead of single weeks?

Yes. In the Grouping dialog, set Number of days to 28 instead of 7, and set Starting at to your period 1 start date. This gives you fixed 4-week reporting periods, useful for most periodic reports. Note this is a simplified 28-day approximation, a true 4-4-5 retail fiscal calendar alternates 4-week and 5-week periods and needs a manual period-mapping helper column instead of straight grouping.

Can I group by week and month at the same time?

Not through the Grouping dialog directly. It only supports one custom day-based level. Add separate Month and Week helper columns and use both as Row fields for a two-level view.

Weekly grouping is one small piece of building an actual reporting dashboard. If you’re working with sales, attendance, or survey data and need it turned into something decision-ready, check out Statssy’s Excel and data analytics resources for dashboard-building help. And if you’re stuck on the day-of-week side of this instead of weekly grouping, here’s our guide on converting dates to day of week in Excel.