Find if a month belongs to a date range

Question

I have a date range and a corresponding value. On a the right-hand side of this data table, I want to separate the value monthly.

Available Data

Result Expected

*I have the limits of the date range any row can have. Ex: Mar 1st to Jun 30th.

I can take the number of days in a month and divide the value by that to get the value to fill in. Problem is automatically deciding the cells which needs to be filled and which has to be marked as 0. The solution I'm looking for is a formula that can be dragged into many more months.

My approach was to check at each cell if the Month Code("Mar", "Apr", "May"....etc) includes in the date range in A and B columns.

I have searched ways to check this and have failed. Is there a way to check if a month includes within a particular date range?

Or is there any other way I can fill the cells from D2 to G6?

*Actual scenario has more than 4 months and more than 6 Rows of data.

Answer 1

This is a formula giving the exact amounts for first row - not sure if that is what you want though:

=(max(0,min($B2,eomonth(datevalue("1-"&left(D1,3)&"2020"),0)))-max($A2,datevalue("1-"&left(D1,3)&"2020"))+1)*$C2/($B2-$A2+1)

Alternatively

=if(min($B2,eomonth(datevalue("1-"&left(D1,3)&"2020"),0))-max($A2,datevalue("1-"&left(D1,3)&"2020"))>=0 [your formula] ,0)