Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
grouping date in pivot table | New Users to Excel | |||
Grouping Date Fields in Pivot Table | Excel Discussion (Misc queries) | |||
Grouping Date Data in a Pivot Table | Excel Worksheet Functions | |||
Grouping By Date in Pivot Table | Excel Worksheet Functions | |||
Pivot table-date grouping | Excel Discussion (Misc queries) |