formula for date

[Guest]

i have two columns
col a col b
q3 2/2/07
q2 10/12/06
I have 4 quarters in a financial year jul-sep, oct-dec, jan-mar and apr-jun
is there a formula I can enter in col a so that when I enter date in col b
then correct quarter will come up in col a
Thank you for your assistance

 

[Roger Govier]

Hi

You posted this question in worksheet functions on 6th Oct.
You had several answers then, including my response of

="Q"&CEILING(MONTH(B1),3)/3

Did they not work for you?

 

[Guest]

The formulaes worked for a calendar year but from that post but I could not
get it to work for the financial year. e.g 12/12/06 came up as q4 and I
needed it to come up as q2.

Thank you.

 

[JE McGimpsey]

Given that the OP wanted the fiscal year to start in July, if the
previous answers were like the one you just posted, they probably didn't
work...

One modification:

="q"&CEILING(MONTH(DATE(2007,MONTH(A11)+6,1)),3)/3

 

[David Biddulph]

Roger,

I haven't spotted the thread you mentioned in worksheet.functions, but
wouldn't your formula need to look more like
="Q"&CEILING(MOD(MONTH(B9)+5,12)+1,3)/3
if it were to satisfy the OPs's description?

 

[Guest]

I have tried this formula but it still does not work.
Col a Col B
Q3 1/02/2007
Q3 2/12/2006
Q3 3/09/2006
Q3 5/4/2007
I entered ="Q"&CEILING(MONTH(DATE(2007,MONTH(A11=6,1)),3)/3
but it gave Q3 and the A11 changed to A12,a13 etc
so I tried $a$11 but still gave Q3 on all lines

Thank you.

 

[JE McGimpsey]

Change the reference to that of your actual cell, e.g.:

="q"&CEILING(MONTH(DATE(2007,MONTH(B2)+6,1)),3)/3


(Note that you entered something rather different that what I posted...)

 

[Guest]

Thank you very much
Your formulae worked perfectly very much appreciated

 

[Roger Govier]

Hi

I'm sure there is an easier way, but this works
="Q"&2+CEILING(MONTH(B1),3)/3-4*(MONTH(B1)>6)