This may be too complicated to explain without showing the financial
spreadsheet...but here goes.

Firstly I have a start date column and end date column for Projects. There
is also a contribution column which shows the total amount of contribution
for that project (e.g. £5,900). Lastly, I have 4 columns which are the
financial years (05/06, 06/07 etc) which at the moment are blank.

I want to create a formula that checks the start and end date and then
apportions the contribution across the financial years.

e.g if the start date and end date was 01/09/05 and 01/09/2006 respectively
and the contribution was £2000 i want a formula in the financial year columns
that splits the money into the relevant year. in this case £1000 in the
05/06 column and £1000 in the 06/07 column.

Can it be done?

I forgot to add the the amount split wouldnt be not be equal.

an example would be:

£5000 contribution for a duration of 18 month starting 01/01/2006 means I
need the spread to be - £1944.44 in year 05/06 (7 months worth) and £3055.56
in 06/07 (11 months worth) as the financial year end is 31/07/2006 hence the
reason for the split.

This could save days of work if it can be done



"Burt" wrote:

This may be too complicated to explain without showing the financial
spreadsheet...but here goes.

Firstly I have a start date column and end date column for Projects. There
is also a contribution column which shows the total amount of contribution
for that project (e.g. £5,900). Lastly, I have 4 columns which are the
financial years (05/06, 06/07 etc) which at the moment are blank.

I want to create a formula that checks the start and end date and then
apportions the contribution across the financial years.

e.g if the start date and end date was 01/09/05 and 01/09/2006 respectively
and the contribution was £2000 i want a formula in the financial year columns
that splits the money into the relevant year. in this case £1000 in the
05/06 column and £1000 in the 06/07 column.

Can it be done?

Burt,

by which criterion do you want the money to be alotted to half-years?
By the number of days/365? So, in a project from 12/17/2004 until
12/31/2005, with a total of 379 days, you want 14/379 to go to 2004 and
365/379 to go to 2005?

Kostis Vezerides


Burt wrote:
I forgot to add the the amount split wouldnt be not be equal.

an example would be:

£5000 contribution for a duration of 18 month starting 01/01/2006 means I
need the spread to be - £1944.44 in year 05/06 (7 months worth) and £3055.56
in 06/07 (11 months worth) as the financial year end is 31/07/2006 hence the
reason for the split.

This could save days of work if it can be done



"Burt" wrote:

This may be too complicated to explain without showing the financial
spreadsheet...but here goes.

Firstly I have a start date column and end date column for Projects. There
is also a contribution column which shows the total amount of contribution
for that project (e.g. £5,900). Lastly, I have 4 columns which are the
financial years (05/06, 06/07 etc) which at the moment are blank.

I want to create a formula that checks the start and end date and then
apportions the contribution across the financial years.

e.g if the start date and end date was 01/09/05 and 01/09/2006 respectively
and the contribution was £2000 i want a formula in the financial year columns
that splits the money into the relevant year. in this case £1000 in the
05/06 column and £1000 in the 06/07 column.

Can it be done?

thats right. In your example it would be 14/379 to go to 2004 and
365/379 to go to 2005?

the only difference is that i am using financial years e.g.

05/06 = 01/08/2005 to 31/07/2006
06/07 = 01/08/2006 to 31/07/2007
07/08 = 01/08/2007 to 31/07/2008

"vezerid" wrote:

Burt,

by which criterion do you want the money to be alotted to half-years?
By the number of days/365? So, in a project from 12/17/2004 until
12/31/2005, with a total of 379 days, you want 14/379 to go to 2004 and
365/379 to go to 2005?

Kostis Vezerides


Burt wrote:
I forgot to add the the amount split wouldnt be not be equal.

an example would be:

£5000 contribution for a duration of 18 month starting 01/01/2006 means I
need the spread to be - £1944.44 in year 05/06 (7 months worth) and £3055.56
in 06/07 (11 months worth) as the financial year end is 31/07/2006 hence the
reason for the split.

This could save days of work if it can be done



"Burt" wrote:

This may be too complicated to explain without showing the financial
spreadsheet...but here goes.

Firstly I have a start date column and end date column for Projects. There
is also a contribution column which shows the total amount of contribution
for that project (e.g. £5,900). Lastly, I have 4 columns which are the
financial years (05/06, 06/07 etc) which at the moment are blank.

I want to create a formula that checks the start and end date and then
apportions the contribution across the financial years.

e.g if the start date and end date was 01/09/05 and 01/09/2006 respectively
and the contribution was £2000 i want a formula in the financial year columns
that splits the money into the relevant year. in this case £1000 in the
05/06 column and £1000 in the 06/07 column.

Can it be done?

Burt,

I am assuming the following layout:

Cells A2:Ax contain start dates.
Cells B2:Bx contain end dates.
Cells C2:Cx contain project budget.
Cells D1:I1 contain the numbers 2003, 2004, etc. 2003 stands for the
fiscal year 8/1/2003-7/31/2004. Clearly you can extend this range for
more years.

In D2 you put the formula:
=$C2/($B2-$A2+1)*SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))=DAT E(E$1,8,1))*(ROW(INDIRECT($A2&":"&$B2))<DATE(E$1+1 ,8,1)))

Copy down and across as necessary.

If you insist on having different labels (e.g. 03/04) you can use the
following variant

=$C2/($B2-$A2+1)*SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))=DAT E(LEFT(E$1,2)+2000,8,1))*(ROW(INDIRECT($A2&":"&$B2 ))<DATE(LEFT(E$1,2)+2000+1,8,1)))

Or, if you prefer the simpler formula:
You leave the headers as I suggest and start everything else from row
3. Leave these numbers in row 1, hide row 1 and supply your labels in
row 2 (visible).

HTH
Kostis Vezerides


Burt wrote:
thats right. In your example it would be 14/379 to go to 2004 and
365/379 to go to 2005?

the only difference is that i am using financial years e.g.

05/06 = 01/08/2005 to 31/07/2006
06/07 = 01/08/2006 to 31/07/2007
07/08 = 01/08/2007 to 31/07/2008

"vezerid" wrote:

Burt,

by which criterion do you want the money to be alotted to half-years?
By the number of days/365? So, in a project from 12/17/2004 until
12/31/2005, with a total of 379 days, you want 14/379 to go to 2004 and
365/379 to go to 2005?

Kostis Vezerides


Burt wrote:
I forgot to add the the amount split wouldnt be not be equal.

an example would be:

£5000 contribution for a duration of 18 month starting 01/01/2006 means I
need the spread to be - £1944.44 in year 05/06 (7 months worth) and £3055.56
in 06/07 (11 months worth) as the financial year end is 31/07/2006 hence the
reason for the split.

This could save days of work if it can be done



"Burt" wrote:

This may be too complicated to explain without showing the financial
spreadsheet...but here goes.

Firstly I have a start date column and end date column for Projects.. There
is also a contribution column which shows the total amount of contribution
for that project (e.g. £5,900). Lastly, I have 4 columns which are the
financial years (05/06, 06/07 etc) which at the moment are blank.

I want to create a formula that checks the start and end date and then
apportions the contribution across the financial years.

e.g if the start date and end date was 01/09/05 and 01/09/2006 respectively
and the contribution was £2000 i want a formula in the financial year columns
that splits the money into the relevant year. in this case £1000 in the
05/06 column and £1000 in the 06/07 column.

Can it be done?

Hi Kostis

Very nice solution!
There might be a typo though as I think the formula in D2 needs to start
with dates from Year D1 not E1

=$C2/($B2-$A2+1)*
SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))=DATE(D$1,8 ,1))*
(ROW(INDIRECT($A2&":"&$B2))<DATE(D$1+1,8,1)))

--
Regards

Roger Govier


"vezerid" wrote in message
oups.com...
Burt,

I am assuming the following layout:

Cells A2:Ax contain start dates.
Cells B2:Bx contain end dates.
Cells C2:Cx contain project budget.
Cells D1:I1 contain the numbers 2003, 2004, etc. 2003 stands for the
fiscal year 8/1/2003-7/31/2004. Clearly you can extend this range for
more years.

In D2 you put the formula:
=$C2/($B2-$A2+1)*SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))=DAT E(E$1,8,1))*(ROW(INDIRECT($A2&":"&$B2))<DATE(E$1+1 ,8,1)))

Copy down and across as necessary.

If you insist on having different labels (e.g. 03/04) you can use the
following variant

=$C2/($B2-$A2+1)*SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))=DAT E(LEFT(E$1,2)+2000,8,1))*(ROW(INDIRECT($A2&":"&$B2 ))<DATE(LEFT(E$1,2)+2000+1,8,1)))

Or, if you prefer the simpler formula:
You leave the headers as I suggest and start everything else from row
3. Leave these numbers in row 1, hide row 1 and supply your labels in
row 2 (visible).

HTH
Kostis Vezerides


Burt wrote:
thats right. In your example it would be 14/379 to go to 2004 and
365/379 to go to 2005?

the only difference is that i am using financial years e.g.

05/06 = 01/08/2005 to 31/07/2006
06/07 = 01/08/2006 to 31/07/2007
07/08 = 01/08/2007 to 31/07/2008

"vezerid" wrote:

Burt,

by which criterion do you want the money to be alotted to
half-years?
By the number of days/365? So, in a project from 12/17/2004 until
12/31/2005, with a total of 379 days, you want 14/379 to go to 2004
and
365/379 to go to 2005?

Kostis Vezerides


Burt wrote:
I forgot to add the the amount split wouldnt be not be equal.

an example would be:

£5000 contribution for a duration of 18 month starting 01/01/2006
means I
need the spread to be - £1944.44 in year 05/06 (7 months worth)
and £3055.56
in 06/07 (11 months worth) as the financial year end is 31/07/2006
hence the
reason for the split.

This could save days of work if it can be done



"Burt" wrote:

This may be too complicated to explain without showing the
financial
spreadsheet...but here goes.

Firstly I have a start date column and end date column for
Projects. There
is also a contribution column which shows the total amount of
contribution
for that project (e.g. £5,900). Lastly, I have 4 columns which
are the
financial years (05/06, 06/07 etc) which at the moment are
blank.

I want to create a formula that checks the start and end date
and then
apportions the contribution across the financial years.

e.g if the start date and end date was 01/09/05 and 01/09/2006
respectively
and the contribution was £2000 i want a formula in the financial
year columns
that splits the money into the relevant year. in this case
£1000 in the
05/06 column and £1000 in the 06/07 column.

Can it be done?