Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Custom Grouping on Pivot Table
I have a pivot table with dates for the column headings. I have grouped these
by month and year but this does not give me the desirable effect. At the moment the dates are grouped as follows: 2003 2004 Jan Feb Mar Apr Jun..... Jan Feb Mar Apr Jun I need to group these by FY so it would like: Last FY YTD Mar Apr May Jun.... Mar Apr May Jun I tried adding an extra field for mm-yy but ended up confusing myself even more....can some help? I am using Excel 97. TIA |
#2
|
|||
|
|||
I had a similar challenge. What I did was add a column to the data table
for the fiscal year and then group the pivot table by FY and Month. Carlos "Edgar Thoemmes" wrote in message ... I have a pivot table with dates for the column headings. I have grouped these by month and year but this does not give me the desirable effect. At the moment the dates are grouped as follows: 2003 2004 Jan Feb Mar Apr Jun..... Jan Feb Mar Apr Jun I need to group these by FY so it would like: Last FY YTD Mar Apr May Jun.... Mar Apr May Jun I tried adding an extra field for mm-yy but ended up confusing myself even more....can some help? I am using Excel 97. TIA |
#3
|
|||
|
|||
I use the same method that Carlos does. Assuming my real dates are in in Col C,
then in my Fiscal Year column in say H2 i would use =IF(MONTH(C2)<4,(YEAR(C2)-1)&"/"&RIGHT(YEAR(C2),2),YEAR(C2)&"/"&RIGHT((YEAR(C2)+1),2)) and copy down. This will give a tag of 2005/06 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Edgar Thoemmes" wrote in message ... I have a pivot table with dates for the column headings. I have grouped these by month and year but this does not give me the desirable effect. At the moment the dates are grouped as follows: 2003 2004 Jan Feb Mar Apr Jun..... Jan Feb Mar Apr Jun I need to group these by FY so it would like: Last FY YTD Mar Apr May Jun.... Mar Apr May Jun I tried adding an extra field for mm-yy but ended up confusing myself even more....can some help? I am using Excel 97. TIA --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.797 / Virus Database: 541 - Release Date: 15/11/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table services | Excel Discussion (Misc queries) | |||
Pivot table grouping | Excel Discussion (Misc queries) | |||
Custom Views in Pivot Table | Excel Discussion (Misc queries) | |||
Can I set my custom views on Pivot table | Excel Discussion (Misc queries) | |||
Problem with Pivot Table Drop-Down Menus | Excel Worksheet Functions |