Thread: Calculating the Quarter Number for Calendar and Fiscal Year

Problem:

Calculating the quarter number corresponding with each of the dates listed in column A.

Solution:

To calculate the quarter number based on a calendar year, use the INT and MONTH functions in the following formula:
=INT((MONTH(A2)-1)/3)+1
To calculate the quarter number based on a fiscal year (starting in September), use the MOD, CEILING, and MONTH functions as shown in the following formula:
=MOD(CEILING(22+MONTH(A2)-9-1,3)/3,4)+1

... at least for me. I had much trouble finding quarter in a FY function elsewhere on the internet. I converted Joseph Rubin's formula above to PHP and use it to break my Wordpress entries into something resembling a quarterly magazine.

In PHP:

$currQuarter = ceil(( date('n') + ( 21 -  ( 9 -1 ))) /3 ) %4 +1 ;

where date('n') is the month number of today. The %4 returns the same as MOD() in Excel. Still assiming 9 (September) is the starting month of our FY.

Thank you, and credit given to ExcelTip.com at http://neches.net/about/2/

John Sinclair