Split cost between between two dates

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 ...

Cost Split.xlsx11 KB

BI & Data Analysis

excel

Formulas and Functions

Haytham Amairah

Silver Contributor

Dec 10, 2018

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.