formula for date
 

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

formula for date
 

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?

--
Regards
Roger Govier



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

formula for date
 

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.

"Roger Govier" wrote:

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?

--
Regards
Roger Govier



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

formula for date
 

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

In article ,
"Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote:

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?

formula for date
 

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?
--
David Biddulph

"Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message
...
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?

--
Regards
Roger Govier

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

formula for date
 

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" wrote:

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

In article ,
"Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote:

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?

formula for date
 

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...)


In article ,
bee wrote:

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" wrote:

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

In article ,
"Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote:

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?

formula for date
 

Thank you very much
Your formulae worked perfectly very much appreciated

"David Biddulph" wrote:

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?
--
David Biddulph

"Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message
...
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?

--
Regards
Roger Govier

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

formula for date
 

Hi

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

--
Regards
Roger Govier



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

"Roger Govier" wrote:

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?

--
Regards
Roger Govier



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