Excel split cost figure between months with partial dates

Question

I have a list of cost figures with start dates and end dates which I need to split between months, I have searched for the solution to this problem but cannot seem to find one that will work with partial months i.e.( startdate:01/01/2015 enddate: 15/04/2015 cost:10000) which would leave figures like Jan:2857, Feb:2857, Mar:2857, Apr:1429.

I have been trying to modify this example: http://www.excel-university.com/excel-formula-to-allocate-an-amount-into-monthly-columns/ but having no luck getting the partial months working.

Any suggestions or help would be most welcome. Thanks in Advance

ab_ever ab_ever · Accepted Answer · 2015-05-15T10:18:43

if you calculate it on daily basis, would it be ok? the result would be:

01.01.2015  01.02.2015  01.03.2015  15.04.2015
 2.857,14    2.857,14    2.857,14    1.428,57

your daily amount is:

=10.000/(DAYS360(startdate;enddate;TRUE)+1)

(be carefull of true and false argument)

under the dates or instead of 2.857,14 etc. insert the formula:

=IF(DAY("your date")>1;DAY("your date");30) * daily amount

This formula assumes that you want to have 30 days in each month:

=IF(DAY(01.01.2015)>1;DAY(01.01.2015);30)

result = 30

=IF(DAY(15.04.2015)>1;DAY(15.04.2015);30)

result = 15

so if months begins with a date different from the 1st it will give you the number of days.

if you want to match months with your startdate and enddate (if i understood your comment correctly), you could do:

=IF(OR(
       AND(MONTH(startdate)=MONTH(your date);YEAR(startdate)=YEAR(your date));
       AND(MONTH(enddate)=MONTH(your date);YEAR(enddate)=YEAR(your date))
      );"match";"no match")

by this you make sure that month and year correspond.

If you want to get the number of days in a month automatically, you could use:

=DAY(DATE(YEAR("your date");MONTH("your date")+1;1)-1)

but this does not assume anymore 30 days, you can change it with if statement

I hope this helps,

Best - AB

Excel split cost figure between months with partial dates

1 Answers