Calculating quarters of a year.
Try this
="Q"&IF(AND(A1=DATE(YEAR(A1),12,7),A1<=DATE(YEAR( A1),12,31)),1,INT((MONTH(A1)+2)/3))
--
Regards,
Peo Sjoblom
"dalymjl" wrote in message
...
I have a spreadsheet in which I keep track of expenditure. I need to
be able to provide end of quarter balances. However there is a
complication.
In any year the first quarter includes a few weeks in December and the
final quarter ends at the end of the first week in December the
following year. Example, in 2008, Quarter 1 began on Dec 12 2007 and
ended Mar 31 2008. The second and third quarters are as normal Apr 1
to Jun 30 and Jul 1 to Sep 30. Quarter 4 is from Oct 1 to Dec 7. (The
reason for this is so that our Accounts Section can have all invoices
paid by year end on Dec 31)
Therefore, when I enter a date in Column A, I'd like Q1, Q2, Q3 or Q4
(based on the requirement set out above) to appear beside it in Column
B.
If it helps, I could insert 2 cells where I could input the start date
(Dec 12 2007) and end date (Dec 7 2008). Then it would only be a
matter of changing these dates annually.
Any help welcome.
MJD
--
dalymjl
|