Quarters
Posted by Tony on February 15, 2002 3:47 AM
I am designing a pivot table that shows my sales by quaters. But my financial year starts April. Is there a way to redifine the quaters list so that qtr1 is April,May,June and not January, February,March.
Posted by Mark W. on February 15, 2002 7:25 AM
Unfortunately Grouping assumes that your fiscal
year is the same as the calendar year. You'll
need to calculate the quarter and store it in
your data list. Here's a formula that you
could use to calculate the quarter:
=CHOOSE(MONTH(ref),4,4,4,1,1,1,2,2,2,3,3,3)
Posted by Bariloche on February 15, 2002 10:18 PM
Tony,
Put a date in A1 and copy the following formula into another cell:
=IF(LEN(A1) > 0,VLOOKUP(MONTH(A1),{1,"Q4";3,"Q1";6,"Q2";9,"Q3";12,"Q4"},2),"") & IF(LEN(A1)>0,IF(OR(MONTH(A1)=1,MONTH(A1)=2)," FY" & RIGHT(YEAR(A1),2)," FY" & RIGHT(YEAR(A1)+1,2)),"")
This illustrates how to use a Vlookup table (embeded, in this case) to identify the quarter that a date is in. If your accounting month does not begin and end on the calendar month days (e.g., February accounting month begins on the 4th, as my companies' did) then you will need to construct a Vlookup table to indicate the start and end dates and the corresponding quarter. Then just add a column to your raw data table which contains a formula to "look-up" the quarter for the corresponding date.
If this isn't clear, repost and I'll give you more specific examples.
enjoy
