finding quarter date information for accounting

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

--

Regards,

Peo Sjoblom


"Sean Atchison" wrote in message
...
Anyone know of a function example on a worksheet that
will return the quarter (1-4) and the month in the
quarter (1-3)?

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)

--

Regards,

Peo Sjoblom


"Peo Sjoblom" wrote in message
...
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

--

Regards,

Peo Sjoblom


"Sean Atchison" wrote in message
...
Anyone know of a function example on a worksheet that
will return the quarter (1-4) and the month in the
quarter (1-3)?

For the months within a quarter, use MOD:

=mod(month(a1),3)

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Peo Sjoblom" wrote in message
...
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

--

Regards,

Peo Sjoblom


"Sean Atchison" wrote in message
...
Anyone know of a function example on a worksheet that
will return the quarter (1-4) and the month in the
quarter (1-3)?

"Fred Smith" wrote...
For the months within a quarter, use MOD:

=mod(month(a1),3)

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.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.