Get Data from semantic model in excel date formatting not working

Hi,GilbertQ ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,dkir .I am glad to help you.
Based on the screenshot you provided, here is my understanding of the information you provided.
1. the connection mode of the semantic model you are connecting to is Import mode.
2. the table you imported into excel is a pivot table ( not )
I am judging from this diagram:.

Here's my tests that I hope will help you:.

select the semantic model

1. choose Insert Table mode

In this mode, you can select the date hierarchy, but the imported tables are normal static tables.

2. Select Insert PivotTable mode

I noticed that Group Selection is grayed out when PivotTable is selected and a lot of functions are not available.

I discussed with my team members and tested different versions of excel (to check if it was an effect of different versions) and the end result was the same as yours: even though the date fields in power bi were set up normally with date hierarchies (successfully grouped), they were all recognized by the system as normal fields in excel!

Here are the versions we tested (none of them recognize the hierarchy successfully)
So I think this is by design and not some kind of issue.
Here are the versions of excel we used for testing

I then consulted relevant issues and articles, and eventually found the explanation.

Here's the article in question:

Excel Pivot Table Error Cannot Group That Selection – Excel Pivot Tables

When Excel connects directly to Power BI Server, this mode of importing data is usually considered OLAP (Online Analytical Processing) mode.
The connection mode used for the test is to connect directly to the data source through the power BI Service.
Therefore in this mode (OLAP) you cannot create hierarchies for date columns.
This is a functional limitation, not an issue.
Fortunately, you can get around this limitation in other ways

You can try selecting the Import data mode to individually import the date columns used by the semantic model and create groups for them, bypassing the restriction in this way.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.