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...
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.
Thanks a lot HansVogelaar