Hi All,
i have a situation where i need to allocate funds or dollar amounts for each
calendar year (columns CY 2010 and CY 2011 and . Below is the table i have:

Start Date Finish Date Total Days Subtotal 2009
2010 2011
1-Mar-09 28-Feb-11 730 $149,398.78 ???
??? ???

What formula do i use for columns 2009, 2010 and 2011 to allocate the $$ for
each Calendar Year?
Thank you in advance.

Here is the better organized way of the table (columns: StarDate, FinishDate,
TotDays, Subtotal, 2009, 2010, 2011):

StartDate FinishDate TotDays Subtotal 2009 2010 2011
1-Mar-09 28-Feb-11 730 $149,398 ??? ??? ???

Thank you in advance.


"Gladiator" wrote:

Hi All,
i have a situation where i need to allocate funds or dollar amounts for each
calendar year (columns CY 2010 and CY 2011 and . Below is the table i have:

Start Date Finish Date Total Days Subtotal 2009
2010 2011
1-Mar-09 28-Feb-11 730 $149,398.78 ???
??? ???

What formula do i use for columns 2009, 2010 and 2011 to allocate the $$ for
each Calendar Year?
Thank you in advance.

Had to translate the formula from the german version, u might have to
verify the punctuation or the spelling of the functions if it's not
working.
It's calculating: subtotal / total Days * days in year 2009:

A1 = StartDate
B2 = FinishDate
D2 = Subtotal

=D2/(B2-A2+1)*SUMPRODUCT((YEAR(ROW(INDIRECT(A2&":"&B2)))=2 009)*1)

Instead of inserting the year u might refer to the header like this: E
$1

Hope this is what you were looking for.

Hubisan

Hubisan, thanks for the response. It did work. But i did change the formula
by locking the year references and referencing the St and Fin dates that are
in another sheet:

=$D2/(Sheet3!$B8-Sheet3!$A8+1)*SUMPRODUCT((YEAR(ROW(INDIRECT(Sheet3 !$A8&":"&Sheet3!$B8)))=E$1)*1)

So far it is working fine unless you have any comments on the changes i made.
Thanks.

"Hubisan" wrote:

Had to translate the formula from the german version, u might have to
verify the punctuation or the spelling of the functions if it's not
working.
It's calculating: subtotal / total Days * days in year 2009:

A1 = StartDate
B2 = FinishDate
D2 = Subtotal

=D2/(B2-A2+1)*SUMPRODUCT((YEAR(ROW(INDIRECT(A2&":"&B2)))=2 009)*1)

Instead of inserting the year u might refer to the header like this: E
$1

Hope this is what you were looking for.

Hubisan



.