Expand records for each month between two dates

Question

I have a few members lists for donations for a campaign. Individual members will to contribute for a duration.

Here is an example:

ID	Name	Donation Amount	Donation Start Date	Donation End Date	Status
1	Name 1	300	1-Sep-2025	30-Sep-2025	Active
2	Name 2	400	1-Sep-2025	31-Oct-2025	Active
3	Name 3	500	1-Sep-2025	30-Nov-2025	Active
4	Name 4	250	1-Sep-2025	31-Dec-2025	Active

I am trying to expand this table like this:

ID	Name	Donation Amount	Donation Date	Status
1	Name 1	300	1-Sep-2025	Active
2	Name 2	400	1-Sep-2025	Active
2	Name 2	400	1-Oct-2025	Active
3	Name 3	500	1-Sep-2025	Active
3	Name 3	500	1-Oct-2025	Active
3	Name 3	500	1-Nov-2025	Active
4	Name 4	250	1-Sep-2025	Active
4	Name 4	250	1-Oct-2025	Active
4	Name 4	250	1-Nov-2025	Active
4	Name 4	250	1-Dec-2025	Active

First person will contribute for only 1 month. Third person will contribute for 3 months and so on. The goal is to make a report where there will have each record for each month automatically. I wish to do it by Excel formula only.

I tried the following formula which give me desired result but I need to process each column inside formula.

=LET(x,DROP(REDUCE("",MAP(D2:D6,E2:E6,LAMBDA(_d,_e,TEXTJOIN(",",1,TEXT(EDATE(_d,SEQUENCE(1,DATEDIF(_d,_e,"m")+1,0)),"dd-mmm-yyyy")))),LAMBDA(a,x,IFERROR(VSTACK(a,TEXTSPLIT(x,",")),""))),1),
HSTACK(TOCOL(IFS(x<>"",A2:A6),3),TOCOL(IFS(x<>"",B2:B6),3),TOCOL(IFS(x<>"",C2:C6),3),TOCOL(IFS(x<>"",x),3)))

Answer 1

Interesting question. REDUCE() is the 1st thing that comes to mind for me:

The formula I used in H1:

=REDUCE(HSTACK(A1:C1,"Donation Date",F1),F2:F5,LAMBDA(x,y,VSTACK(x,IF({1,1,1,0,1},HSTACK(TAKE(y:A5,1,3),0,y),{1,1,1,1}*EDATE(@+D5:y,SEQUENCE(DATEDIF(@+D5:y,@+E5:y+1,"m"))-1)))))

So what this does is that I traverse the cells in column F in order. Through the use of some implicit intersection we can play around with different ranges connected to this initial cell. The trick with IF() is important to fill down the correct values in their respective columns.

Answer 2

Here is one way one could try, not that elegant like the other answers but it works, :

---

=REDUCE({"ID", "Name", "Donation Amount", "Donation Date", "Status"}, E2:E5, 
 LAMBDA(_a, _b,
 LET(_c, @+D5:_b,
     _d, SEQUENCE(DATEDIF(_c, _b+1, "ym")),
     _f, LAMBDA(_g, CHOOSEROWS(CHOOSECOLS(A2:F5, _g), @+A5:_b*_d^0)),
     VSTACK(_a, HSTACK(_f({1, 2, 3}), EDATE(_c, _d-1), _f(6))))))

Another alternative way:

=LET(
     _a, E2:E5,
     _b, DATEDIF(D2:D5, _a, "m"),
     _c, SEQUENCE(, MAX(_b+1), @_b),
     _d, LAMBDA(_e, TOCOL(IF(_c<=_b, _e, 0/0), 2)),
     _f, HSTACK(_d(A2:A5), _d(B2:B5), _d(C2:C5), _d(EDATE(+D2:D5, _c)), _d(F2:F5)),
     VSTACK({"ID","Name","Donation Amount","Donation Date","Status"}, _f))

Or, bit shorter may be the of the above:

=LET(
     _a, +D2:D5,
     _b, DATEDIF(_a, E2:E5+1, "ym"),
     _c, SEQUENCE(, MAX(_b)),
     _d, LAMBDA(_e, INDEX(A2:F5, TOCOL(IFS(_b>=_c, A2:A5), 3), _e)),
     HSTACK(_d({1,2,3}), TOCOL(EDATE(_a, _c/(_b>=_c)-1), 3), _d(6)))