» 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:
- Essentials of Investments with Standard & Poor's Educational Version of Market Insight + PowerWeb + Stock Trak Coupon
- Writing Excel Macros with VBA, 2nd Edition
- Real Estate Loopholes: Secrets of Successful Real Estate Investing
- Windows XP for Dummies
- The Laws of Money, The Lessons of Life: Keep What You Have and Create What You Deserve
- PowerPoint® 2002 For Dummies®
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