Link to home
Start Free TrialLog in
Avatar of exp vg
exp vg

asked on

Excel - Pivot - Group Dates into Months Format

I have an excel spreadsheet, inserted into a pivot table. One column has a list of individual days of the month. Once the pivot table is created, I group the individual days as month.

The days are listed as 3/1/2015, 3/2/2015, etc.

However, when I group into the pivot table, the months are listed as simply Mar, Apr, etc.

Is there a way to add the year when grouping individual days into months in an excel pivot table?

Thank you.
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

how's ur original worksheet looks like? do u hav column of "Month Year" for the dates? u can simply create this column and use it in ur pivot table then.

the formula for this "Month Year" column could be like:

=TEXT(yourdate, "MMM YYYY")
Avatar of exp vg
exp vg

ASKER

This is close, but what I am hoping for is to just add a year extension in the pivot table itself after grouping.

Example:

Mar

becomes

Mar 15 or Mar 2015

Thank you.
how's your pivot table currently looks like? can you upload one sample here?
Avatar of exp vg

ASKER

I cannot upload the source data, but here is the resulting pivot table:

             Mar      Apr      May      Jun      Jul      Grand Total
Loc A      1              3                      4
Loc B            1             1                2
Loc C            6       15       3      5         29
Loc D      262      366      382      370      124         1504
Grand Total      263      373      400      374      129      1539
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of exp vg

ASKER

Thank you.