» 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:
- Infectious Greed: How Deceit and Risk Corrupted the Financial Markets
- Windows XP All-in-One Desk Reference For Dummies
- Flipping Properties: Generate Instant Cash Profits in Real Estate
- The Financial Numbers Game: Detecting Creative Accounting Practices
- Microsoft Word Version 2002 Step By Step (With CD-ROM)
- The McGraw-Hill 36-Hour Course in Finance for Nonfinancial Managers
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