Value calculation based on date range

[Rakhee Asarpota]

Hi,
I need a formula that calculates the amount based on the date range inserted.

Eg: i have a value $4000 for period 01/07/18 to 31/03/2019 = thats 8 months split.
I want dates to be dynamic range that auto populates the months columns based on the months in the range (value divided by number of months in the relevant month columns).

I have attached the sheet as an example.

Can someone guide me

Many thanks

 

[AlanSidman]

I have read your request and looked at your spreadsheet. I have no idea what you want to happen. Suggest you explain using your spreadsheet as an example. We are not mind readers here and do need a clear and precise explanation of your issue and expected results to provide you with a solution.

 

[Rakhee Asarpota]

Apologies that my message seemed vague.
In the attached sheet i have to manually adjusted the formula to end on the month as per the end date or else it would carry on without knowing the end date.

Example:
A10:B10 (Date range of 8 months) I should be able divide the value (E10) to range in F10:I10 only, as I10 is ending date (Mar'19) and shouldn't recognise any amount balance left for further split.

As the start and end dates will vary, the range should be dynamic to put values in the right bucket months as per the # of months and dates.

hope i was able to explain the situation.

Appreciate your input.

Thanks

 

[Rakhee Asarpota]

Can anyone guide me on how I can achieve or find a solution to this sheet....please ?

 

[bosco_yip]

Maybe...................

1] C7, copied down :

=DATEDIF(A7,B7,"M")+1

2] F7, copied down :

=ROUND($E7/$C7*(DATEDIF($A7,--"31/12/2018","M")+1),0)

3] G7, copied across to R7 and all copied down :

=IF(G$6<=$B7,ROUND($E7/$C7,0),"")

Regards
Bosco