formula
 

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.

formula
 

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

formula
 

Hi

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

--
Regards
Roger Govier



"bee" wrote in message
...
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.

formula
 

On Oct 6, 12:47 am, bee wrote:
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")

formula
 

Or a bit shorter

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

--
Regards
Roger Govier



"Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message
...
Hi

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

--
Regards
Roger Govier



"bee" wrote in message
...
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.

formula
 

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

formula
 

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

--
Regards
Roger Govier



"Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message
...
Or a bit shorter

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

--
Regards
Roger Govier



"Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message
...
Hi

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

--
Regards
Roger Govier



"bee" wrote in message
...
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.