Split Value based on the date

[subodhsapkota]

TOTAL Date Start Date Finish Jan Feb March April
$155,193.94 07/01/2018 15/05/2018
$149,553.94 17/03/2018 10/04/2018


I have three excel columns

Now the total $ value has to be divided onto different months based on the number of days on that particular month

ie. for the first case if it includes three weeks of January and 2 weeks of February, then the total cost should include proportinately 3/5 of $155,193,94 on Jan and 2/5 of $155,193,94 for February

Is there a way to do that ?


In general the final output will have total amount $ value intended for that particular month

Edit:: Attached sample file on what is intended

Values in RED are the value we need.

[samba_ravi]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Bo_Ry]

Please try at K5 and copy over the table

=$H5*MAX(MIN(EOMONTH(1&K$4&YEAR($I5),0),$J5)-MAX(1&K$4&YEAR($I5),$I5)+1,0)/($J5-$I5+1)