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.
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.
