Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculating quarters of a year.
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating quarters of a year.
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 __ 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... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating quarters of a year.
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... |
#5
|
|||
|
|||
Many thanks for your help everyone.
Excel banter to the rescue as usual! regards MJD Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How should I get the # of quarters in a year. | Excel Worksheet Functions | |||
Calculating year to date... | Excel Discussion (Misc queries) | |||
year quarters === Financial Year | Excel Discussion (Misc queries) | |||
how do I convert a dates in a year quarters in a year? | Excel Discussion (Misc queries) | |||
Calculating by individual months of the year | Excel Discussion (Misc queries) |