Excel Pivot Table "Group Field" disabled

Question

The setup is Excel 2013 PivotTables, getting data from a SSAS2014 cube.

The requirement is to allow users to aggregate measures over the Date dimension, using a strange kind of week in which Friday is the first day of the week.

Our time dimension has way too many hierarchies already, so I was hoping that the built-in Excel pivot-table "Group Field" functionality would allow users to show data at the Day level, and then aggregate by "weeks starting with Friday" - without us having to build a new Week hierarchy into the dimension and reprocess the entire cube.

No go, though. I can't get this "Group Field" command (in the PivotTable Tools ribbon, Analyze section) to ever be enabled when I click on a date field. (To be precise, I click on a particular day in the pivot-table; I click on the attribute over in the Rows section of the PivotTable Fields box; I click on the row header - no difference).

Here's what I've tried:

1. The Days level of the dimension shows in the Pivot-Table in US format (mm/dd/yyyy). I thought this data, in combination with my machine locale settings (European date format dd/mm/yyyy) might be confusing Excel, so I changed the locale to US through Control Panel and reopened the .xlsx file. No difference.
2. Checked the underlying dimension attribute. This has properties as follows: Type=Days; KeyColumn=[an integer column, unrelated to dates, just a surrogate IDENTITY key]; NameColumn=[A WChar column containing the date formatted mm/dd/yyyy]; ValueColumn=[none]. OK, I thought - there's nothing Date-like there, maybe that's why Excel can't figure out how to group this data.
3. Opened up a pivot-table on AdventureWorks to see what that does. Using the Date attribute of the Date dimension - Excel still doesn't enable "Group Field". Looking at the underlying dimension design, the Date attribute has slightly different properties: Type=Date; KeyColumn=[an integer column of form 20150807 for today, for example]; NameColumn=[a Wchar column]; ValueColumn=[a column of type Date].

So I'm confused. Format Cells is a good quick way to find out if Excel is understanding cell contents as dates: but Format Cells doesn't work in the Row Labels of the PivotTable (either against AdventureWorks or against my cube).

Is there actually a way to make "Group Field" work on date dimensions in Pivot-Tables/SSAS? I hoped I'd find out through AdventureWorks, but that doesn't work either.

The closest parallel I can find on-line is here, where people suggest that the problem is Excel not understanding data as dates. But the answers in that thread are all aimed at people who pivot-tabling on data imported to Excel, rather than against SSAS:

Solution

I was having the same problem. I think that I have it fixed.

1. Go to File > Options > Advanced
2. Scroll down to the Data section
3. Uncheck "Prefer the Excel Data Model when creating Pivot Tables, QueryTables, and Data Connection"

This fixed it for me. Good Luck!