Formula for Austrlian financial Year/Calendar and Quarters

[TripB]

Hello,

I'm after a formula that will return the financial year/calendar year and quarter from a date field


Date Calendar Year Calendar Year
01/01/2016 to 30/03/2016 1st Qtr 2016 3rd Qtr 2015/2016
01/04/2016 to 30/06/2016 2nd Qtr 2016 4th Qtr 2015/2016
01/07/2016 to 30/09/2016 3rd Qtr 2016 1st Qtr 2016/2017
01/10/2016 to 31/12/2016 4th Qtr 2016 2nd Qtr 2016/2017

and so on

Date Field will be one column with dates per day:

Date
01/01/2016
02/01/2016
03/01/2016 etc

Any help is appreciated.
Cheers
Haydn

 

[Tetra201]

Try these:

for calendar qtr/year =ROUNDUP(MONTH(A1)/3,0)&CHOOSE(ROUNDUP(MONTH(A1)/3,0),"st","nd","rd","th")&" Qtr "&YEAR(A1)

for financial qtr/year =ROUNDUP(MONTH(EDATE(A1,-6))/3,0)&CHOOSE(ROUNDUP(MONTH(EDATE(A1,-6))/3,0),"st","nd","rd","th")&" Qtr "&YEAR(EDATE(A1,-6))&"/"&YEAR(EDATE(A1,-6))+1

 

[TripB]

Try these:

for calendar qtr/year =ROUNDUP(MONTH(A1)/3,0)&CHOOSE(ROUNDUP(MONTH(A1)/3,0),"st","nd","rd","th")&" Qtr "&YEAR(A1)

for financial qtr/year =ROUNDUP(MONTH(EDATE(A1,-6))/3,0)&CHOOSE(ROUNDUP(MONTH(EDATE(A1,-6))/3,0),"st","nd","rd","th")&" Qtr "&YEAR(EDATE(A1,-6))&"/"&YEAR(EDATE(A1,-6))+1

Thanks Tetra. Work a treat!

Cheers
Haydn

 

[Tetra201]

You are welcome.