Calculating quarters of a year.

[dalymjl]

I have a spreadsheet in which I keep track of expenditure. I need t
be able to provide end of quarter balances. However there is
complication.
In any year the first quarter includes a few weeks in December and th
final quarter ends at the end of the first week in December th
following year. Example, in 2008, Quarter 1 began on Dec 12 2007 an
ended Mar 31 2008. The second and third quarters are as normal Apr
to Jun 30 and Jul 1 to Sep 30. Quarter 4 is from Oct 1 to Dec 7. (Th
reason for this is so that our Accounts Section can have all invoice
paid by year end on Dec 31)

Therefore, when I enter a date in Column A, I'd like Q1, Q2, Q3 or Q
(based on the requirement set out above) to appear beside it in Colum
B.

If it helps, I could insert 2 cells where I could input the start dat
(Dec 12 2007) and end date (Dec 7 2008). Then it would only be
matter of changing these dates annually.

Any help welcome.

MJ

 

[Héctor Miguel]

hi, !

assuming you won't put a date in column A *out* of your annual date-range -?-
you might want to give a try to the following formula (i.e.)

[B2] ="Q"&match(a2,--{"2007-12-12";"2008-04-01";"2008-07-01";"2008-10-01"})

hth,
hector.

__ OP __

 

[Peo Sjoblom]

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

 

[Héctor Miguel]

hi (again), !

or... convert the constant array -> ,--{"2007-12-12";"2008-04-01";"2008-07-01";"2008-10-01"}
(i.e.) into a fixed range with each date (say: C1:C4) and modify the formula to...

[B2] ="Q"&match(a2,$c$1:$c$4)

(still one single function *call*)

hth,
hector.

assuming you won't put a date in column A *out* of your annual date-range -?-
you might want to give a try to the following formula (i.e.)

[B2] ="Q"&match(a2,--{"2007-12-12";"2008-04-01";"2008-07-01";"2008-10-01"})

__ OP __

I have a spreadsheet in which I keep track of expenditure.
I need tobe 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...

 

[dalymjl]

Many thanks for your help everyone.

Excel banter to the rescue as usual!

regards

MJD

hi (again), !

or... convert the constant array ->
,--{"2007-12-12";"2008-04-01";"2008-07-01";"2008-10-01"}
(i.e.) into a fixed range with each date (say: C1:C4) and modify the
formula to...

[B2] ="Q"&match(a2,$c$1:$c$4)

(still one single function *call*)

hth,
hector.
-

assuming you won't put a date in column A *out* of your annual date-range -?-
you might want to give a try to the following formula (i.e.)

[B2] ="Q"&match(a2,--{"2007-12-12";"2008-04-01";"2008-07-01";"2008-10-01"})

__ OP __-

I have a spreadsheet in which I keep track of expenditure.
I need tobe 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...

--