Split cost between between two dates

Occasional Visitor

I have a spreadsheet where i'm putting a start date and end date in two columns, in the next column i have a total cost and then the months in the columns after


What i am trying to achieve is if a user enters a start date for example 10-Oct and end date being 12-Dec, i would like the cost to be split as follows


20 days in Oct

30 days in Nov

12 days in Dec


i have managed to work out the number of days between the two dates being 63 days, Now i want to put a formula in each month so if the dates fall into that month the cost for each day in that month is calculated.


I've attached an example spreadsheet


I tried so many different ways and struggled


Thanks in advance

3 Replies



To do that, you need this thing:


But please note that the remaining days in Oct is 22, not 20.


To make the solution easier, I've changed the month headers to the first date of each month but I changed the format to show only the month.


Cost Split.png


Hope that helps

Absolutely amazing, would have taken me ages otherwise! thank you
This formula does not work when my start date it and end date are not in the same calendar year. for example my start date is 14 October 2021 and end date is is 4th July 2022.
Generally I use MSP for doing this task but currently to work in Excel. Is there a way?