formula for date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bee - 10 Oct 2007 12:24 GMT

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

Reply to this Message

Roger Govier - 10 Oct 2007 12:43 GMT

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?

Signature

Regards
Roger Govier

>i have two columns
> col a col b
[quoted text clipped - 5 lines]
> then correct quarter will come up in col a
> Thank you for your assistance

Reply to this Message

bee - 10 Oct 2007 13:00 GMT

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.

> Hi
>
[quoted text clipped - 14 lines]
> > then correct quarter will come up in col a
> > Thank you for your assistance

Reply to this Message

Roger Govier - 10 Oct 2007 14:24 GMT

Hi

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

Signature

Regards
Roger Govier

> The formulaes worked for a calendar year but from that post but I could
> not
[quoted text clipped - 22 lines]
>> > then correct quarter will come up in col a
>> > Thank you for your assistance

Reply to this Message

JE McGimpsey - 10 Oct 2007 13:04 GMT

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

> Hi
>
[quoted text clipped - 4 lines]
>
> Did they not work for you?

Reply to this Message

bee - 10 Oct 2007 13:28 GMT

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.

> 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
[quoted text clipped - 12 lines]
> >
> > Did they not work for you?

Reply to this Message

JE McGimpsey - 10 Oct 2007 13:34 GMT

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

> I have tried this formula but it still does not work.
> Col a Col B
[quoted text clipped - 24 lines]
> > >
> > > Did they not work for you?

Reply to this Message

David Biddulph - 10 Oct 2007 13:14 GMT

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?

Signature

David Biddulph

> Hi
>
[quoted text clipped - 4 lines]
>
> Did they not work for you?

>>i have two columns
>> col a col b
[quoted text clipped - 6 lines]
>> then correct quarter will come up in col a
>> Thank you for your assistance

Reply to this Message

bee - 10 Oct 2007 13:36 GMT

Thank you very much
Your formulae worked perfectly very much appreciated

> Roger,
>
[quoted text clipped - 21 lines]
> >> then correct quarter will come up in col a
> >> Thank you for your assistance

Reply to this Message