financial Qtrs

[lesleylkp]

Hi awesome lot I found a formula for adding Quarters from dates, but unfortunately it calculates based on Jan - Dec. Does anyone know a formula which will do it by Financial Quarters from dates as in - April to March?? this is the formula I have - =YEAR(J3)& " Q" & INT(MONTH(J3)/4)+1

many thanks x

 

[vletm]

lesleylkp
=eomonth(J3,3)

 

[lesleylkp]

=eomonth(J3,3)

Hi, i just get this - i need it to say 2014 Q3 or Q3 2014 xx

16/12/2014

42094

 

[vletm]

lesleylkp
... then You should write clear what needs ... check below
="Q" &ROUNDUP(MONTH(EOMONTH(J3,3))/3,0) & " " & YEAR(EOMONTH(J3,3))

 

[bosco_yip]

Maybe,

A1: 16/12/2014

[B1] =YEAR(A1)&" Q"&INT(MOD((MONTH(A1)-7)/3+1,4))+1

and, B1 return : 2014 Q3

Regards
Bosco

 

[lesleylkp]

lesleylkp
... then You should write clear what needs ... check below
="Q" &ROUNDUP(MONTH(EOMONTH(J3,3))/3,0) & " " & YEAR(EOMONTH(J3,3))

I thought i had, but thank you very much for responding, i;'ll check oout your suggestion

 

[lesleylkp]

Maybe,

A1: 16/12/2014

[B1] =YEAR(A1)&" Q"&INT(MOD((MONTH(A1)-7)/3+1,4))+1

and, B1 return : 2014 Q3

Regards
Bosco

Bosco that works a treat many thanks <3 xxxx

 

[Peter Bartholomew]

Given a four-digit year 'yyyy', a month number 'm' and the first month of the financial year 'm0',
= (yyyy - (m<m0)) & " Q" & (1 + MOD(FLOOR(m-m0, 3)/3, 4))
gives the quarter, identified by the year in which Q1 occurred.