Pivot table date grouping

[Jonathan589]

XL2003. I am analysing data imported from another app. I want to keep the
different dates in the records, but I want to group them into Months in the
pivot table. If there are only a few it lets me, but if there are more than
255 it won't. Can I somehow specify the grouping levels before I drag the
dates onto the field?

 

[Luke M]

Note that the 255 is due to an hard XL limitation. (see XL specifications in
help file)

For your scenario, create a helper column with a new header (NewMonth). In
first cell, input a formula similar to:
=DATE(YEAR(A2),MONTH(A2),1)
Format cell as:
mmmm
Fill down as needed.
You can now use this new field in your Pivot Table to group your data
together. Note that if you don't care about year, replace the YEAR function
with a 1.

 

[Jonathan589]

Thanks for this Luke, it's one of my workarounds. Another is using Trunc() to
get rid of the fractions in dates, because 7 Jul 2009 13:45:23 differs from 7
Jul 2009 15:17:46! I'd been hoping there was a setting somewhere so I'd not
have to bother ...