Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping DAtes in Pivot Tables
When you group dates by year in a pivot table it is always by calendar years
i.e. January to December. This isn't very useful if your financial year runs for example from March to February. Is there a way of customising the grouping within a pivot table? -- TheBerbs |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping DAtes in Pivot Tables
There's nothing built into the pivot table that will allow you to group
dates by fiscal year. You could add a column to the source data, in which you calculate the Fiscal year and month. Then, add that field to the pivot table. berby65 wrote: When you group dates by year in a pivot table it is always by calendar years i.e. January to December. This isn't very useful if your financial year runs for example from March to February. Is there a way of customising the grouping within a pivot table? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping DAtes in Pivot Tables
Thanks, I thought as much. Unfortunately the source data is an external
database rather than a spreadsheet. I will have to come up with another work around. It is a shame becuase apart from this, the grouping function is very useful. -- TheBerbs "Debra Dalgleish" wrote: There's nothing built into the pivot table that will allow you to group dates by fiscal year. You could add a column to the source data, in which you calculate the Fiscal year and month. Then, add that field to the pivot table. berby65 wrote: When you group dates by year in a pivot table it is always by calendar years i.e. January to December. This isn't very useful if your financial year runs for example from March to February. Is there a way of customising the grouping within a pivot table? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping DAtes in Pivot Tables
We also run on a fiscal year (April - March) and I set up a custom list to
sort data by months within the fiscal year. Tools -- Options -- Custom Lists. -- Elisabeth "berby65" wrote: Thanks, I thought as much. Unfortunately the source data is an external database rather than a spreadsheet. I will have to come up with another work around. It is a shame becuase apart from this, the grouping function is very useful. -- TheBerbs "Debra Dalgleish" wrote: There's nothing built into the pivot table that will allow you to group dates by fiscal year. You could add a column to the source data, in which you calculate the Fiscal year and month. Then, add that field to the pivot table. berby65 wrote: When you group dates by year in a pivot table it is always by calendar years i.e. January to December. This isn't very useful if your financial year runs for example from March to February. Is there a way of customising the grouping within a pivot table? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping DAtes in Pivot Tables
Hi
In addition to what Elizabeth has posted regarding Custom sort order, here in the UK the fiscal year runs from 06/04/2006 to 05/04/2007 If in your grouping, you set the start date to be 06/04/2006 and end date to 05/04/2007, and Group by Year and Month then your table would show <06/04/2006 2006 Apr May .................. .................... . 2007 Mar Apr 05/04/2007 Where April in the respective years would show data for only the respective qualifying days. -- Regards Roger Govier "berby65" wrote in message ... Thanks, I thought as much. Unfortunately the source data is an external database rather than a spreadsheet. I will have to come up with another work around. It is a shame becuase apart from this, the grouping function is very useful. -- TheBerbs "Debra Dalgleish" wrote: There's nothing built into the pivot table that will allow you to group dates by fiscal year. You could add a column to the source data, in which you calculate the Fiscal year and month. Then, add that field to the pivot table. berby65 wrote: When you group dates by year in a pivot table it is always by calendar years i.e. January to December. This isn't very useful if your financial year runs for example from March to February. Is there a way of customising the grouping within a pivot table? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get the grouping dialogue box to appear in Pivot Tables? | Excel Worksheet Functions | |||
Pivot Tables - How can I "reset" the selections in "Row Field"? | Excel Discussion (Misc queries) | |||
Dynamically changing several pivot tables at once | Excel Discussion (Misc queries) | |||
Ho to Delete "Ghost" Pivot Tables | Excel Discussion (Misc queries) | |||
Problems with updating category names in pivot tables | Excel Discussion (Misc queries) |