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.
Hi anonymous_me
there is an easy solution for your problem, you just have to create a custom cell style.
1. Format one cell exactly the way you need (number format, fill color etc.
2. Select that cell and then open the menu "Home | Cell Styles | New Cell Style...":
3. Confirm (or adjust) the settings in the style-window. You can give it also a style name:
4. Done. Now you can just select the new style for your cells
Unfortunately, you cannot assign a keyboard shortcut to it, but it's still only one click with the mouse.
12 Replies
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
