Split cost per year in ratio to project duration within that year

[MrPokemans]

Hey guys,


I have the costs of 7000 projects with a duration of multiple years, for example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]start[/TD]
[TD]end[/TD]
[TD]cost[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]1-1-16[/TD]
[TD]1-1-17[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]5-5-16[/TD]
[TD]1-1-17[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]10-10-16[/TD]
[TD]10-10-17[/TD]
[TD]1000[/TD]
[/TR]
</tbody>[/TABLE]

I need the costs of each project per year (prices are estimates):

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]cost in 2016[/TD]
[TD]cost in 2017[/TD]
[TD]cost in 2018[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]999[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]998[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]310[/TD]
[TD]690[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

What formulas should I use for B2:D4 in this table?

Preferably without VBA and array formulas because the file is already very heavy.

Another difficulty are the 'leap years', meaning that 2016 has 366 days.

Thanks in advance!

 

[jorismoerings]

Hi,

try this:

Book1
	A	B	C	D	E	F	G	H
1		start	end	cost		2016	2017	2018
2	Project 1	1-1-2016	1-1-2017	1000		997,2752	2,724796	0
3	Project 2	5-5-2016	1-1-2017	1000		995,8678	4,132231	0
4	Project 3	10-10-2016	10-10-2017	1000		226,776	773,224	0
Sheet1

Cell Formulas
Range	Formula
F2	=MAX((EOMONTH(DATE(F$1,1,1),11)-DATE(F$1,1,1))+1-MAX($B2-DATE(F$1,1,1),0)-MAX(EOMONTH(DATE(F$1,1,1),11)-$C2,0),0)*($D2/($C2-$B2+1))

 

[MrPokemans]

A big thanks to you. Question answered, topic closed imo.