Forum Discussion
Date Formatting in pivot table
Date format in Pivot table is not working in filters or in columns/rows. (it has always worked so far, and in this particular case it's not)
If I go to the source data, it is a date format.
It lists all dates that exist in the data, instead of grouping them by year or by month? So, if I want to exclude or include any particular month, I have to tick or un-tick every single date in that month in filter. Or, the data is not displaying any grouped format in that month.
Could you please help? Thank you so much.
Do you want to create an extra column in your source data?
=TEXT(theDate, "yyyyMM")
No, data is already there.
When I select that column (date format) in either Filter or column or row, pivot table doesn't look like it recognizes that column in date format, and treat it like a text.
When date data is treated as a date, pivot table shows it as hierarchy format, year, then month, then day, which can be expanded to show what's below and contracted to only show year or month.
So, one click will let me select or deselect the entire month or year.
Hope this makes sense.
- Yes, it makes sense.
Date format is a format, not the value represents the year or month value. The value is still the date value.
Hope this makes sense.
Please see attached, Thank you for looking into this.
Hello Grace15,
Please see the attached- the file has 2 sheets- each sheet is a different option for you
option 1: in the Pivot Table fields ==> FILTERS ==> click on (published dates) ==>Field settings
==> number formats ==> Dates ==> Mar-12
option 2: add a new column with the desired date format
I hope this will be what you have been seeking
