Pivot Table Date Format

[MAEKO]

Hello,
I am taking a values on the system automatically. Date format is DD.MM.YYYY
My PC date setting is DD.MM.YYYY. When i wanted to create pivot table, dates seems ungroup.
But i click two times every line, i don't know what did it change in background, it is working and seems group at pivot.
Because i want to see group for month, quarter and year.
Could you please share your experience?
Thanks.
It seems the below.

I want to see the below.

[#2 · Accepted solution]

@MAEKO 

That sounds like Excel originally treated the dates as text.

You can try the following:

• Select the dates in the source data.
• On the Data tab of the ribbon, click Text to Columns.
• Select Delimited, then click Next >> twice.
• In step 3, select DMY from the Date drop down.
• Click Finish.

To group by month, quarter and year in the pivot table:

• Right-click any of the (ungrouped) dates in the pivot table.
• Select Group... from the context menu.
• Select Months, Quarters and Years.
• Click OK.

[#3]

Thanks a lot @HansVogelaar 

[#4]

@HansVogelaar What if when I try to Group the options "Grouping by" doesn't show? (All the columns labels are Date)

[#5]

@Rodolfo1950 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#6]

@HansVogelaar 

Thank you for your quick reaction. As you can see, I try to group columns by months but no "Group by..." option is shown. What is interesting is that until yesterday morning the Pivot Table was fine showing columns grouped by months. Suddenly the grouping disappeared, each column is for a single day, and I am unable to do the grouping again.

[#7]

@Rodolfo1950 

Your screenshot shows the Group... option:

[#8]

Yes, and it shows than I don't have option "by ,,,". When I click "Group" all the columns are grouped in a single one.

[#9]

@Rodolfo1950 

There is no Group By item in the context menu.

What happens if you select a single date and right-click it, then select Group... from the context menu?

[#10]

@HansVogelaar Same story: just "Group", no "Group by". I have tried with other combinations of dates: same result.

[#11]

@Rodolfo1950 

I repeat my request from yesterday:

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#12]

@Rodolfo1950 I am having the same exact issue as you. The table was fine until today. Now the option to group by Months and Years is not available. My work's Microsoft updates happen on Wednesday nights. I wonder if there is an issue related to the most recent update. 

[#13]

@Joseph0425 

That's low probability such function as grouping disappeared with update. But if you share exact Excel platform/version someone here, if have the same, could check.

Most probably reason if at least one of values in the field you group was added as text. Even if it looks like date. Please note, formatting doesn't change the data type. If you apply, for example, date format to the cell with text it will be text.