yearly quarters

Posted by keith on January 04, 2002 9:53 AM

Hi,

I have a spreadsheet question. Our company uses a spreadsheet to track all
of it's jobs. I use the following formula to determine what quarter of the
year a jobs falls into (for tax purposes)...
=IF(L134="",0,ROUNDUP(MONTH(L134) /3,0))

However our year end is really at the end of February, so our book keeper
has mentioned that it would be much easier for her if the formula above was
able to determine what quarter with respect to out year end that job falls
into... Q1 = Mar-May, Q2 = Jun-Aug, Q3 = Sep-Oct, Q4 = Dec-Feb.

Any ideas how this can be done?

cheers,
keith

Posted by Aladin Akyurek on January 04, 2002 10:17 AM

Keith --

Try:

=IF(LEN(L134) > 0,VLOOKUP(MONTH(L134),{1,4;3,1;6,2;9,3;12,4},2),"")

Aladin

========

Posted by Bariloche on January 04, 2002 10:45 PM

just for my own amazement

I decided to extend Aladin's formula:

=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)),"")

With the above formula in some cell and the contents of A1 being some date like 1/5/2002, the result of this formula would be "Q2 FY02". When you get to March the fiscal year will increment by 1 and so for 3/1/2002 you would get "Q1 FY03".

Thanks Aladin for the inspiration.