Pivot table grouping by week - can't get it to start on Monday

Question

In Excel 2016, I'm trying to set up a pivot table which has a Date-format column and group it by weeks.

Ungrouped, the data looks like this:

I'm then trying to group it using the following settings:

The data then appears like this:

As you can see, the grouping starts on 6th Sep 2017 which was a Wednesday. Presumably this is because where my data starts.

Unfortunately, this renders the data somewhat useless. I need the data grouped by weeks starting on a Monday.

I've tried changing the Starting With and Ending With settings to:

• The Monday before 06 Sep
• The Monday after 06 Sep
• The Sunday after 23 Jan
• The Monday after 23 Jan
• The Monday before 23 Jan
• The Sunday before 23 Jan

Regardless of what I try, I can't get the weeks to group starting on a Monday.

Any suggestions would be greatly appreciated!

Answer 1

Since you have tried all these,,

I've tried changing the Starting With and Ending With settings to:

The Monday before 06 Sep

The Monday after 06 Sep

The Sunday after 23 Jan

The Monday after 23 Jan

The Monday before 23 Jan

The Sunday before 23 Jan

Now my suggestion is,

While Grouping, either you set the Start Date as 04/09/2017 or 02/01/2017 and "Un-Check the Start Date Checkbox and Click OK".

Check the Screen Shot I've tested your Data & Group on Date by using 02-01-2017 as Start Date, it's Working, now the Start Date after Group is 04-09-2017 is the Monday.

Hope this help you.