Spilled Array based on different Time Periods and applied periods - Help Needed

James_Buist My first response used a brute-force method to compare the target periods against all dates between the earliest start date and the oldest end date, which can be quite labor intensive and may affect performance with a larger dataset/date range. It was also originally written as a generalized method to handle any start day, including the 30th and 31st, which is probably overkill for your needs.

Another approach could be to generate an array of thunks (uncalled lambda functions), which contain the required sequence of dates for each line number. Then, the array(s) can be broadcast across (and down) to fill the entire output grid for each period, and MAP can be used to loop through the resulting 3D array, calling each applicable thunk (function) as needed.

Assuming I've understood your request correctly, it could look something like this:

=LET(
    periodStart, G2:R2,
    periodEnd, G3:R3,
    interval, B5:B12,
    startDate, C5:C12,
    endDate, D5:D12+(interval>1),
    duration, ((YEAR(endDate)-YEAR(startDate))*12+MONTH(endDate)-MONTH(startDate)+1)/interval,
    seqλ, MAP(startDate,duration,interval,LAMBDA(sd,d,i,LAMBDA(x,y,
        LET(a,EDATE(sd,SEQUENCE(d,,0,i)),--IFERROR(OR((a>=x)*(a<=y)),0))))),
    MAP(IF({1},periodStart,interval),IF({1},periodEnd,interval),IF({1},seqλ,periodEnd),LAMBDA(ps,pe,λ,λ(ps,pe)))
)

It's not the easiest method to understand, but the amount of data being handled is much less than in my first approach. 😉

EDIT: my first two attempts only checked if the start date landed between the period start and period ending dates, which was based on your manually entered "expected results"; however, after re-reading your instructions in the workbook where you said, "if a start or end date hits the middle of a time period... then it is fine just to apply it to the entire period", I'm thinking this might be more appropriate:

=LET(
    periodStart, G2:R2,
    periodEnd, G3:R3,
    interval, B5:B12,
    startDate, C5:C12,
    endDate, D5:D12,
    duration, ((YEAR(endDate)-YEAR(startDate))*12+MONTH(endDate)-MONTH(startDate)+1)/interval,
    seqλ, MAP(startDate,duration,interval,LAMBDA(sd,d,i,LAMBDA(x,y,
        LET(a,EDATE(sd,SEQUENCE(d,,0,i)),b,EOMONTH(a,i-1),--IFERROR(OR(((a>=x)*(a<=y))+((b>=x)*(b<=y))),0))))),
    MAP(IF({1},periodStart,interval),IF({1},periodEnd,interval),IF({1},seqλ,periodEnd),LAMBDA(ps,pe,λ,λ(ps,pe)))
)

Check them out and see if either one does what you want...