Formula - distributing data over months

[Stecky]

I am trying to create a formula that distributes the data in Y1-Y20 (these are years) by month over the Rebate Start and End Date Range.

The challenge is the data changes year to year.

Can anyone help?

https://docs.google.com/spreadsheets/d/1WNP71eWU2hUl4s8PllPEWPehWiimkkiiwVZUNWpkVyo/edit?usp=sharing


Thanks,
Stecky

 

[Stecky]

[TABLE="width: 100"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Mths Delay[/TD]
[TD]Y1[/TD]
[TD]Y2[/TD]
[TD]Y3[/TD]
[TD]Y4[/TD]
[TD]Y5[/TD]
[TD]Y6[/TD]
[TD]Y7[/TD]
[TD]Y8[/TD]
[TD]Y9[/TD]
[TD]Y10[/TD]
[TD]1/18[/TD]
[TD]2/18[/TD]
[TD]3/18[/TD]
[TD]4/18[/TD]
[TD]5/18[/TD]
[TD]6/18[/TD]
[TD]7/18[/TD]
[TD]8/18[/TD]
[TD]9/18[/TD]
[TD]10/18[/TD]
[TD]11/18[/TD]
[TD]12/18[/TD]
[TD]1/19[/TD]
[TD]2/19[/TD]
[TD]3/19[/TD]
[TD]4/19[/TD]
[TD]5/19[/TD]
[TD]6/19[/TD]
[TD]7/19[/TD]
[TD]8/19[/TD]
[TD]9/19[/TD]
[TD]10/19[/TD]
[TD]11/19[/TD]
[TD]12/19[/TD]
[/TR]
[TR]
[TD]01/01/2018[/TD]
[TD]12/31/2027[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]06/01/2018[/TD]
[TD]05/31/2029[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]

I see that a lot of people have looked but I may have not done a good job explaining what I am trying to do.

If the only data I have is (Start Date / End Date / Y1-Y10). The Y are years (12 months of data).

I am trying to put the values in Y1 through Y10 on a timeline by month. I have 1000s of rows of these.

My biggest challenge is the data changing by year as I already have a formula that works if the data does not change through the years (seen below).

IF(AND(month>=Start Date, month<=End Date, value, 0) Value being that the number in Y1-Y10 does not change. Month being the date on the timeline.

Hope this helps. Thank you

Stecky