Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table by Financial Year
I've got a spreadsheet with a large quantity of dates that cross over several
financial years. Is there a simple way to produce a pivot table to group these dates by financial year, ie from Jul 06 to Jun 07, then Jul 07 to Jun 08 etc? The dates are entered as dd/mm/yyyy format. Any assistance would be greately appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table by Financial Year
Assuming dates are in coloumns.
First sort the data by date. Insert a coloumn for year keyin year coloumn based on your requirement (i.e., jul 06 to Jun 07) ( you may simply do this by filtering date coloumn) Finally do a pivot based on year coloumn. regards rajesh "fn450" wrote: I've got a spreadsheet with a large quantity of dates that cross over several financial years. Is there a simple way to produce a pivot table to group these dates by financial year, ie from Jul 06 to Jun 07, then Jul 07 to Jun 08 etc? The dates are entered as dd/mm/yyyy format. Any assistance would be greately appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table by Financial Year
An easier way is to Group the dates by year.
Once you've created the pivot table, select the Date field heading & right click; one of the options will be Group & the default is Month, just need to amend this to Year. However, this only works if the data in the date column is all dates - blanks or text will prevent this happening, in which case you'd have to adopt Rajesh's solution (which is th eway I used to use until I found out about Grouping!) Hope this helps, Colin Foster "rajesh" wrote: Assuming dates are in coloumns. First sort the data by date. Insert a coloumn for year keyin year coloumn based on your requirement (i.e., jul 06 to Jun 07) ( you may simply do this by filtering date coloumn) Finally do a pivot based on year coloumn. regards rajesh "fn450" wrote: I've got a spreadsheet with a large quantity of dates that cross over several financial years. Is there a simple way to produce a pivot table to group these dates by financial year, ie from Jul 06 to Jun 07, then Jul 07 to Jun 08 etc? The dates are entered as dd/mm/yyyy format. Any assistance would be greately appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing year grouping in pivot table | Excel Discussion (Misc queries) | |||
year quarters === Financial Year | Excel Discussion (Misc queries) | |||
Pivot table - multiple records with same year?? | Excel Discussion (Misc queries) | |||
How to compare a date with financial year | Excel Discussion (Misc queries) | |||
year function and financial years | Excel Worksheet Functions |