If function - combined with calculating value

[Chelala]

Hi,
I trying to do a straight line calculation based on number of days passed on contract time. and present values quarterly bases.
attached example sheet shows following;
D7 & E7 are the start and end dates
G7 is Contract Value
I7 till P7 are the amounts I need to calculate based on the number of days in each quarter.
considering first quarter (Q1); 1/1/2018 till 3/31/2018
and so on.

thanks in advance for time and efforts.

 

[bosco_yip]

Try,

In I7, copied across and down :

=MAX(0,MIN(EOMONTH(DATE(MID(I$5,4,4),INDEX({1,4,7,10},MID(I$5,2,1)),1),2),$E7)-MAX(DATE(MID(I$5,4,4),INDEX({1,4,7,10},MID(I$5,2,1)),1),$D7)+1)/($E7-$D7+1)*$G7

Regards
Bosco