» Calculating the Quarter Number for Calendar and Fiscal Year
CATEGORY - Excel Date & Time Formulas
VERSION - All Microsoft Excel Versions
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
Book Store:
Recommended Books:
- F1 Get the Most out of Excel! The Ultimate Excel tip Help Guide
- Finance and Accounting for Nonfinancial Managers
- Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
- Wall Street Journal Guide to Understanding Money and Investing
- Financial Statement Analysis with S&P insert card
In PHP:
[CODE]$currQuarter = ceil(( date('n') + ( 21 - ( 9 -1 ))) /3 ) %4 +1 ;[/CODE]
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 [URL="http://neches.net/about/2/"]http://neches.net/about/2/[/URL]
John Sinclair