Formula for Quarter based on Dates

[nandhamnk]

Dear All,


Our accounting period is from April to Mar for every year.I need a formula for Quarter based on the dates.

Below is the example:


07/04/2011

Formula should be Q1-FY11-12.


Thanks for the help.

 

[Debraj]

Hi nandhamnk!


Please use Formula as

="Q" & CEILING(MONTH(A1)/4,1) & "-FY" & TEXT(A1,"YY")  & "-" & TEXT(A1,"YY")+1

Regards,

Deb

 

[nandhamnk]

Sorry Debraj Roy,I think i have not propoerly explained it to you.

Following are the dates and corresponding should be the answer:


07/02/2012 Q4-FY11-12

07/04/2011 Q1-FY11-12

07/07/2011 Q2-FY11-12

07/12/2011 Q3-FY11-12

 

[nazmul_muneer]

Nandhamnk,

Think your data from A2

Try this formula

="Q"&IF(ROUNDDOWN((MONTH(A2)-1)/3,0)=0,"4-FY-"&TEXT(A2,"YY")-1&"-"&TEXT(A2,"YY"),ROUNDDOWN((MONTH(A2)-1)/3,0)&"-FY-"&TEXT(A2,"YY")&"-"&TEXT(A2,"YY")+1)


Regards,

--Muneer

 

[nazmul_muneer]

According to your Financial Year April to March type data from A2


07-Jan-12

07-Feb-12

07-Mar-12

07-Apr-12

07-May-12

07-Jun-12

07-Jul-12

07-Aug-12

07-Sep-12

07-Oct-12

07-Nov-12

07-Dec-12

07-Jan-13

07-Feb-13

07-Mar-13

copy the formula given by me and paste to B2


Regards,


Muneer

 

[nandhamnk]

Thanks Muneer.It works.Thanks for the support.

 

[nazmul_muneer]

ooooOOOOk


Thank you

 

[sdsurzh]

Hi Nandha,


Can you please try my formula also:


=IF(ROUNDDOWN(MONTH(A1)/4,0)=1,"Q1-FY"&YEAR(A1)&"-"&YEAR(A1)+1,IF(ROUNDDOWN(MONTH(A1)/4,0)=2,"Q2-FY"&YEAR(A1)&"-"&YEAR(A1)+1,IF(ROUNDDOWN(MONTH(A1)/4,0)=3,"Q3-FY"&YEAR(A1)&"-"&YEAR(A1)+1,"Q4-FY"&YEAR(A1)&"-"&YEAR(A1)+1)))


Thanks,

Suresh Kumar S

 

[nandhamnk]

Thanks Suresh.It also works,but the year comes to 2012-13.

 

[nazmul_muneer]

Dear Suresh,

Your formula and Debraj's formula returns same result.

Actually Nandhamnk's year starts from 1st April of current year to 31st March of next year. Such as

Financial Year 2012-2013= 01/Apr/2012 to 31/Mar/2013

Financial Year 2011-2012= 01/Apr/2011 to 31/Mar/2012


Regards,


--Muneer

 

[nandhamnk]

Yes you are correct Muneer.

 

[Haseeb A]

Try also,


="Q"&LOOKUP(MONTH(A1),{1,4;4,1;7,2;10,3})&"-FY"&TEXT(A1+275,"YY")-1&TEXT(A1+275,"-YY")