Transform multiple date ranges into continuous dates list through Dynamic Array

djclements, PeterBartholomew1 Patrick2788 thank you all for the answers provided. If it were possible, I would have tagged all of them as "Marked as Solution".  

I offer a recursive solution. I enjoy the challenge of coming up with recursive solutions because there's not a lot technical documentation from Microsoft so it often feels like the wild west of Excel.  The limit of this function is noted below.  The limit can be increased even more if I nest functions and eliminate parameters but I can't forsee unpackings needing to be done with tables encompassing more than a few thousand rows (I could be wrong!).

//Unpack a table by recursion. Function presumes table
//includes col1: Start Date, col2: End Date, col3: Amount.
//Recursion limit: 2,047 rows in packed table.

UnPackλ = 

LAMBDA(table,
    LET(
        next,TAKE(table,1),
        start,TAKE(next,,1),
        diff,1+INDEX(next,,2)-start,
        dates,SEQUENCE(@diff,,@start),
        amount,TAKE(next,,-1)/SEQUENCE(@diff,,@diff,0),
        resize,VSTACK(DROP(table,1),HSTACK(dates,amount)),
        IF(diff<0,DROP(table,,-1),UnPackλ(resize))))

It is reasonably easy to distribute one payment over a period, but life gets much harder when, as in this case, there are multiple amounts.  This is because Microsoft implemented the helper functions such as MAP and SCAN on the assumption that a scalar value would be returned at each step.  Whenever you need to return an array at each step, Excel will throw a #CALC! error. ☹️

OliverScheurichhas addressed this by calling REDUCE to accumulate a growing array of array results using VSTACK (his calculation is included within attached the workbook).  I have used a more complicated approach, but the complexity is hidden within the function MAPλ.  For small problems my approach shows little advantage except, perhaps, by hiding complexity.  For larger problems ~1000+ values, the approach has been found to be 10x faster than REDUCE/VSTACK.

The final calculation takes things a step further by grouping amounts by date and summing any overlaps.

=LET(
    DISTRIBUTEλ, LAMBDA(startLst, endLst, amount,
        LET(
            duration, 1 + endLst - startLst,
            day, SEQUENCE(duration, 1, startLst),
            amt, IF(day, amount / duration),
            HSTACK(day, amt)
        )
    ),
    stacked, MAPλ(Table1[StartDate], Table1[EndDate], Table1[Amount], DISTRIBUTEλ),
    dates, TAKE(stacked, , 1),
    amounts, TAKE(stacked, , -1),
    GROUPBY(dates, amounts, SUM)
)

Another option you could try with your sample file:

=LET(
    beg, Table1[StartDate],
    end, IF(ISBLANK(Table1[EndDate]),beg,Table1[EndDate]),
    dur, end-beg+1,
    amt, Table1[Amount]/IF(dur>0,dur,1),
    seq, SEQUENCE(,MAX(end)-MIN(beg)+1,MIN(beg)),
    tst, (seq>=beg)*(seq<=end),
    HSTACK(TOCOL(IFS(tst,seq),2),TOCOL(IFS(tst,amt),2))
)

Or perhaps:

=LET(
    beg, Table1[StartDate],
    end, IF(ISBLANK(Table1[EndDate]),beg,Table1[EndDate]),
    dur, end-beg+1,
    amt, Table1[Amount]/IF(dur>0,dur,1),
    seq, SEQUENCE(,MAX(dur),0),
    tst, dur>seq,
    HSTACK(TOCOL(IFS(tst,beg+seq),2),TOCOL(IFS(tst,amt),2))
)

1. Assume your data is in columns A, B, and C:
   • StartDate in column A
   • EndDate in column B
   • Amount in column C
2. Create a list of all dates within the ranges:
   • In a new column, use the following formula to generate a list of all dates:

     =SEQUENCE(MAX(B:B) - MIN(A:A) + 1, 1, MIN(A:A), 1)

     This formula creates a sequence of dates from the earliest start date to the latest end date.
3. Calculate the amount per day for each date:
   • In another new column, use the following formula to calculate the amount per day for each date:

     =SUMPRODUCT((A:A<=D2)*(B:B>=D2)*(C:C/(B:B-A:A+1))) 

     Here, D2 is the cell reference for the first date in your sequence. This formula checks if each date falls within the start and end dates and then divides the amount by the number of days in the range.

4. Combine the results:
   • You can now have a table with the dates and the corresponding amounts per day.

Here's a more detailed example:

• Column D (Date) is generated using the SEQUENCE formula.
• Column E (Amount Per Day) is calculated using the SUMPRODUCT formula.