Forum Discussion
Solved
Pivot Table Date Format
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.
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?
Thanks a lot HansVogelaar
