| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE ORDER FOR QTR 1 -4
I new to pivot tables. I'm using a financial year which starts from April and
ends at March, how do can I change the pivot table to read from these months. Currently it defaults from Jan to Dec. I'm using Office 2003. Thanks -- DAVE |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DATE ORDER FOR QTR 1 -4
When you're grouping by those dates???
I just add another column to the original data table and determine the quarter there. I like this formula in general--if the fiscal year starts on the first of month number #: ="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3) So if the fiscal year starts on April 1st, then I'd use: ="FY"&YEAR(A1)-(MONTH(A1)<4)&"-Q"&INT(1+MOD(MONTH(A1)-4,12)/3) I also like this style of result: FY2009-Q1 DJRHUTCH wrote: I new to pivot tables. I'm using a financial year which starts from April and ends at March, how do can I change the pivot table to read from these months. Currently it defaults from Jan to Dec. I'm using Office 2003. Thanks -- DAVE -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DATE ORDER FOR QTR 1 -4
Dave thanks very much for your help. It works for me.
-- DAVE "Dave Peterson" wrote: When you're grouping by those dates??? I just add another column to the original data table and determine the quarter there. I like this formula in general--if the fiscal year starts on the first of month number #: ="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3) So if the fiscal year starts on April 1st, then I'd use: ="FY"&YEAR(A1)-(MONTH(A1)<4)&"-Q"&INT(1+MOD(MONTH(A1)-4,12)/3) I also like this style of result: FY2009-Q1 DJRHUTCH wrote: I new to pivot tables. I'm using a financial year which starts from April and ends at March, how do can I change the pivot table to read from these months. Currently it defaults from Jan to Dec. I'm using Office 2003. Thanks -- DAVE -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DATE ORDER FOR QTR 1 -4
Hi
Dave has given you what is the best solution - using an extra column in your source data. You could, however, do it within the PT. First, Group by Months, not Quarters. Then, with Months as a Row field, select the 3 months representing your Q1, by holding down Control and clicking on each cell. Right clickGroupname the resulting Group1 as FY2009-Q1 Repeat process allocating the other 9 months to their respective quarters. -- Regards Roger Govier "DJRHUTCH" wrote in message ... Dave thanks very much for your help. It works for me. -- DAVE "Dave Peterson" wrote: When you're grouping by those dates??? I just add another column to the original data table and determine the quarter there. I like this formula in general--if the fiscal year starts on the first of month number #: ="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3) So if the fiscal year starts on April 1st, then I'd use: ="FY"&YEAR(A1)-(MONTH(A1)<4)&"-Q"&INT(1+MOD(MONTH(A1)-4,12)/3) I also like this style of result: FY2009-Q1 DJRHUTCH wrote: I new to pivot tables. I'm using a financial year which starts from April and ends at March, how do can I change the pivot table to read from these months. Currently it defaults from Jan to Dec. I'm using Office 2003. Thanks -- DAVE -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DATE ORDER FOR QTR 1 -4
And continue doing this for each FY <vbg.
Roger Govier wrote: Hi Dave has given you what is the best solution - using an extra column in your source data. You could, however, do it within the PT. First, Group by Months, not Quarters. Then, with Months as a Row field, select the 3 months representing your Q1, by holding down Control and clicking on each cell. Right clickGroupname the resulting Group1 as FY2009-Q1 Repeat process allocating the other 9 months to their respective quarters. -- Regards Roger Govier "DJRHUTCH" wrote in message ... Dave thanks very much for your help. It works for me. -- DAVE "Dave Peterson" wrote: When you're grouping by those dates??? I just add another column to the original data table and determine the quarter there. I like this formula in general--if the fiscal year starts on the first of month number #: ="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3) So if the fiscal year starts on April 1st, then I'd use: ="FY"&YEAR(A1)-(MONTH(A1)<4)&"-Q"&INT(1+MOD(MONTH(A1)-4,12)/3) I also like this style of result: FY2009-Q1 DJRHUTCH wrote: I new to pivot tables. I'm using a financial year which starts from April and ends at March, how do can I change the pivot table to read from these months. Currently it defaults from Jan to Dec. I'm using Office 2003. Thanks -- DAVE -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DATE ORDER FOR QTR 1 -4
lol
I did preface it with Dave has given you what is the best solution Good to see the wit is still alive and well. Cheers Dave. -- Regards Roger Govier "Dave Peterson" wrote in message ... And continue doing this for each FY <vbg. Roger Govier wrote: Hi Dave has given you what is the best solution - using an extra column in your source data. You could, however, do it within the PT. First, Group by Months, not Quarters. Then, with Months as a Row field, select the 3 months representing your Q1, by holding down Control and clicking on each cell. Right clickGroupname the resulting Group1 as FY2009-Q1 Repeat process allocating the other 9 months to their respective quarters. -- Regards Roger Govier "DJRHUTCH" wrote in message ... Dave thanks very much for your help. It works for me. -- DAVE "Dave Peterson" wrote: When you're grouping by those dates??? I just add another column to the original data table and determine the quarter there. I like this formula in general--if the fiscal year starts on the first of month number #: ="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3) So if the fiscal year starts on April 1st, then I'd use: ="FY"&YEAR(A1)-(MONTH(A1)<4)&"-Q"&INT(1+MOD(MONTH(A1)-4,12)/3) I also like this style of result: FY2009-Q1 DJRHUTCH wrote: I new to pivot tables. I'm using a financial year which starts from April and ends at March, how do can I change the pivot table to read from these months. Currently it defaults from Jan to Dec. I'm using Office 2003. Thanks -- DAVE -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DATE ORDER FOR QTR 1 -4
It's half alive! (My wit, that is!)
Roger Govier wrote: lol I did preface it with Dave has given you what is the best solution Good to see the wit is still alive and well. Cheers Dave. -- Regards Roger Govier "Dave Peterson" wrote in message ... And continue doing this for each FY <vbg. Roger Govier wrote: Hi Dave has given you what is the best solution - using an extra column in your source data. You could, however, do it within the PT. First, Group by Months, not Quarters. Then, with Months as a Row field, select the 3 months representing your Q1, by holding down Control and clicking on each cell. Right clickGroupname the resulting Group1 as FY2009-Q1 Repeat process allocating the other 9 months to their respective quarters. -- Regards Roger Govier "DJRHUTCH" wrote in message ... Dave thanks very much for your help. It works for me. -- DAVE "Dave Peterson" wrote: When you're grouping by those dates??? I just add another column to the original data table and determine the quarter there. I like this formula in general--if the fiscal year starts on the first of month number #: ="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3) So if the fiscal year starts on April 1st, then I'd use: ="FY"&YEAR(A1)-(MONTH(A1)<4)&"-Q"&INT(1+MOD(MONTH(A1)-4,12)/3) I also like this style of result: FY2009-Q1 DJRHUTCH wrote: I new to pivot tables. I'm using a financial year which starts from April and ends at March, how do can I change the pivot table to read from these months. Currently it defaults from Jan to Dec. I'm using Office 2003. Thanks -- DAVE -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trying to calculate end date using order date and lead time variab | Excel Worksheet Functions | |||
| How stop Excel file UK date order changing to US order in m.merge | Excel Discussion (Misc queries) | |||
| Sorting by date order | Excel Worksheet Functions | |||
| Workbook date order | New Users to Excel | |||
| sort column in date order dd/mm/yy | Excel Discussion (Misc queries) | |||
Linear Mode
