formula for date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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