Split cost between between two dates

[Hervinder]

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

[#2]

Hi,

To do that, you need this thing:

=IF(AND(MONTH(F6)>=MONTH($A$7),MONTH(F6)<=MONTH($B$7)),
IF(MONTH(F6)=MONTH($A$7),DAY(EOMONTH($A$7,0))-DAY($A$7)+1,
IF(MONTH(F6)=MONTH($B$7),DAY(DAY($B$7)),DAY(EOMONTH(F6,0)))),0) 

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.

Hope that helps

[#3]

Absolutely amazing, would have taken me ages otherwise! thank you

[#4]

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?