Pivot table date grouping
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? |
Pivot table date grouping
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jonathan589" wrote: 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? |
Pivot table date grouping
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 ... "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jonathan589" wrote: 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? |
All times are GMT +1. The time now is 06:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com