formula

[Guest]

is there any formula I could use so that when i enter the date in coulmn b
the quarter comes up in column a. I am trying to enter if dates between
01/01/2007 and 31/03/2007 equals q1 and then if between 1/4/07 to 30/6/07
then equals q2 etc etc for q3 and q4.
column a column b
q1 3/02/07
Thank you fro your help.

 

[cubbybear3]

Try this (I think it will work):

=IF(MONTH(B1)<=3,"Q1",IF(AND(MONTH(B1)>=4,MONTH(B1)<=6),"Q2",IF(MONTH(B1)>=10,"Q4","Q3")))

-pb

 

[Roger Govier]

Hi

One way
=INT((MONTH(A1))/3)+(1*MOD(MONTH(A1),3)<>0)

 

[joeu2004]

is there any formula I could use so that when i enter the date
in coulmn b the quarter comes up in column a.

In A1:

="Q" & text(roundup(month(B1)/3,0),"0")

 

[Roger Govier]

Or a bit shorter

=CEILING(MONTH(A1),3)/3

 

[Rick Rothstein \(MVP - VB\)]

is there any formula I could use so that when i enter the date in coulmn b

the quarter comes up in column a. I am trying to enter if dates between
01/01/2007 and 31/03/2007 equals q1 and then if between 1/4/07 to 30/6/07
then equals q2 etc etc for q3 and q4.
column a column b
q1 3/02/07
Thank you fro your help.

This should do it...

="Q"&ROUNDUP(MONTH(B1)/3,0)

Rick

 

[Roger Govier]

Sorry, only just noticed that your dates are in column B, and you want a Q
in from of the quarter number
="Q"&CEILING(MONTH(B1),3)/3