Forum Discussion
Pivot Table Date Format
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.
HansVogelaar What if when I try to Group the options "Grouping by" doesn't show? (All the columns labels are Date)
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?
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.
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.
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.
- Yes, and it shows than I don't have option "by ,,,". When I click "Group" all the columns are grouped in a single one.
