I have an dumb and probably an easy question to answer. I have data I placed into a pivot table that has a date as a filter, the filter is not grouping them to year>Month level but only lists each individual date. Any thoughts on how to get the dates to group in the filter
Attached is an example of the data
ok you can do two things:
1) modify your Source data (data sheet) to include the dates formatted to "mmm - yyyy"
2) move the Date field to the Row Labels then right click on a single date then click Group then choose your preference then moved back the Date field into your Report filter
If I've helped U pls click on d *Add Reputation
Tried that but you can't choose dates only the month.
Example
Jun
06-01
06-02
06-03
ok then you will have to go to your data sheet and add this formula in column D:
=IF(A2="","",TEXT(MONTH(A2)*29,"mmm")&" "&YEAR(A2))
adjust your pivot source data to Data!$A$1:$D$32113
Isn't there any way to get the date to group in the filter instead of having two filters
i'm afraid not. i guess it's possible via VBA.
have you thought about taking the Type in Column Labels and Date in Row Labels..?
This way the data would become more logical and grouping of date would be more easy.
I am still not sure about how you want to group the dates but you can have a look on what I have done.
hope this helps.
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
you can also try to take type in Row labels and Dates in Column Labels and then Group the dates as you like them to.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks