Posts Tagged ‘Excel 2010’

Formulas To Determine Quarters For Calendar And Fiscal Years (XL2000-2010)

Sunday, March 6th, 2011

If you ever need to determine in which quarter of the year a date falls, it’s pretty easy, right? Jan-March is Quarter 1, April-June is Quarter 2, etc…

But what if you need to determine this value for many date cells in your worksheet? Or maybe you have formula in your worksheet whose calculation is based on the quarter. Here’s a formula that makes determining which quarter of the year a date falls a snap.

=ROUNDUP(MONTH(A2)/3,0)

In this example, A2 is the cell containing a date.

Formula to determine quarter of year

In many areas of business it is often necessary to determine in which quarter of a Fiscal Year a date falls. Here is a formula that you can use…

=MOD(CEILING(22-FY_Start_month+ MONTH(Cell_Ref),3)/3,4)+1

In this formula, the variables are FY_Start_Month, which is the month in which your Fiscal Year begins and Cell_Ref is the cell containing the date you want to evaluate.

In this example, the fiscal year begins in October, and the date you want to evaluate is in cell A2. So the formula is…

=MOD(CEILING(22-10+MONTH(A2),3)/3,4)+1

Formula to determine fiscal quarter of year