Fiscal year quarter formula error

[jayb8020]

Formula for our Our fiscal first quarter 2015 (ending <chron style="color: rgb(0, 0, 0); font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 18px;">April 30, 2014</chron>) and fiscal year 2015 (ending <chron style="color: rgb(0, 0, 0); font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 18px;">January 31, 2015</chron>):

Feb 2015 is First month of FY2016 that is Q1-16 and Jan 2016 is last month of FY2016 that is Q4-16


=CONCATENATE("Q",ROUNDUP(MONTH(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)))/3,0))&YEAR(A1)+1
With above formula ,I am not able to get the correct output for January month, Help Please.

 

[gaz_chops]

Welcome to the forum.

Try

="Q"&LOOKUP(MONTH(A1),{1,4;2,1;5,2;8,3;11,4})&YEAR(A1)+LOOKUP(MONTH(A1),{1,-1;2,0})

 

[bosco_yip]

Or this :

="Q"&INT(MOD((MONTH(A1)-5)/3+1,4))+1&"-"&YEAR(A1)-(MONTH(A1)=1)+1

 

[Oeldere]

or make a table with the months and the responding quartal.

after that use vlookup to find the quartal.

 

[gaz_chops]

Or this :

="Q"&INT(MOD((MONTH(A1)-5)/3+1,4))+1&"-"&YEAR(A1)-(MONTH(A1)=1)+1

Don't think you need the +1 at the end.

 

[bosco_yip]

Don't think you need the +1 at the end.

You are correct, it can be this :

="Q"&INT(MOD((MONTH(A1)-5)/3+1,4))+1&"-"&YEAR(A1)+(MONTH(A1)>1)