» 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:
- The One Page Business Plan: Start With a Vision, Build a Company!
- Managerial Accounting: Tools for Business Decision Making, WebCT, 2nd Edition
- Windows XP for Dummies
- Keys to Reading an Annual Report (Barron's Business Keys)
- Business Analysis and Valuation: Using Financial Statements, Text and Cases
- Rich Dad's Guide to Investing: What the Rich Invest in, That the Poor and the Middle Class Do Not!
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