Calculate cost based on number of days within a period

Hello,

I was hoping someone could help me. I have inherited a spreadsheet that has some IF arguments that don't seem to be calculating correctly. It seems that it is using the total amount of days in the date period instead of how many days in each specific month fall within the date period.

This is the formula used =IF(S$1<$B26,0,IF(($E26-S$1+$B26)*$F26<0,0,($E26-S$1+$B26)*$F26))

The formulas in S11:AD38 (highlighted in yellow) should calculate how many days between the specific dates for that row which appear in columns B & C fall into the specific month. Then it should multiply the number of days from the date period that fall within that month by the cost per day in column F.

For example the latest entry in row 26 is showing the July cost as $6,783. This should in fact be $275.28 as there are 31 days in July and all 31 days of July fall within the column B & C dates for that row of 18/5/2018 - 1/9/2020. So the formula in this instance should be calculating 31 days X $8.88.

This calculation should apply per row dependent on how many days of each date range fall into each month in S11:AD38, calculating by whatever Cost per day figure appears for that corresponding date range/row.

Thanks
Rooboyz

Thanks so much Dave that is fantastic! Lifesaver.