Forum Discussion
Date Formatting in pivot table
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.
But, sometimes that's what I see from pivot table with date format. Actually majority of cases I work with.
Pivot table has the ability to group and build hierarchy structure with dates, so it's easier to control the data.
So, there is no way that I could control and change?
- Oh. I am sorry to say that I do not know this feature. Would you minding telling me how I can group the date in year, month, and day?
Thanks.I don't, excel does.... and I'm trying to figure out how to make excel do it when I need....
As the group of "Day" is, for Excel, the day in a year. If you filter by it, to Excel, you are supposed to filter the data by a particular day in the year. This situation is as same as you apply filter on table. e.g. You filter by gender first, and you filter First name later, and Excel will shows all firstname no matter which gender you select. Same case in PivotTable.
If you want, a workaround is to create an extra column, apply the below formula
=DAY(theDate)
For grouping, you use the original date column for grouping Years and Months, and move the new day column below the group of months.
Another approach is to use Slicer for the Days grouping.
Hope that meet your needs.