finding quarter date information for accounting

  • Thread starter Thread starter Sean Atchison
  • Start date Start date
S

Sean Atchison

Anyone know of a function example on a worksheet that
will return the quarter (1-4) and the month in the
quarter (1-3)?
 
if you define the first quarter as 01/01 - 03/31
second as 04/01 - 06/30 and so on you can use this

=VLOOKUP(MONTH(A1),{1,"Qtr1";4,"Qtr2";7,"Qtr3";10,"Qtr4"},2)

where A1 holds the date
 
of course where I work the 1st quarter starts on 04/01/03, then you can use

=VLOOKUP(MONTH(A1),{1,"Qtr4";4,"Qtr1";7,"Qtr2";10,"Qtr3"},2)
 
For the months within a quarter, use MOD:

=mod(month(a1),3)
Click to expand...

Not

=1+MOD(MONTH(A1)-1,3)

?

And for quarter, no need for a lookup.

="Qtr"&INT((MONTH(A1)+2)/3)

And if the fiscal year begins on dd-mm,

="Qtr"&INT(1+MOD(MONTH(A1)-MM-(DAY(A1)<DD),12)/3)

with one caveat: if, for example, the fiscal year began on 31-Aug, then 1-Dec
rather than 30-Nov would be the beginning of the 2nd quarter, and similarly for
1-Mar 3rd qtr.
 
Back
Top