| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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") |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
