Given a StartDate, an EndDate, and a schedule like this in Google Sheets:
Day of Month | Amt | Info |
---|---|---|
1 | $800.00 | Rent |
1 | $155.00 | Electric |
10 | $58.00 | Credit Card |
16 | $87.00 | Student Loans |
23 | $19.99 | Netflix |
31 | $78.00 | Water |
Create a list of dates between StartDate/EndDate that generates real dates for each day of month in the schedule.
For example, StartDate = 11/01/2024 and EndDate = 12/31/2024 produces:
Date | Amt | Info |
---|---|---|
11/1/2024 | $800.00 | Rent |
11/1/2024 | $155.00 | Electric |
11/10/2024 | $58.00 | Credit Card |
11/16/2024 | $87.00 | Student Loans |
11/23/2024 | $19.99 | Netflix |
11/30/2024 | $78.00 | Water |
12/1/2024 | $800.00 | Rent |
12/1/2024 | $155.00 | Electric |
12/10/2024 | $58.00 | Credit Card |
12/16/2024 | $87.00 | Student Loans |
12/23/2024 | $19.99 | Netflix |
12/31/2024 | $78.00 | Water |
Bonus (not required but would be nice)
If the day of the month in the schedule is greater than the number of days in the current month, then map the scheduled day to the last day of the current month.
Generating Dates
I can use the Sequence function to generate dates between StartDate/EndDate:
=SEQUENCE(EndDate-StartDate+1,1,StartDate)
The problem is that this generates all dates in between and I see no way to reference the schedule.
How can this be accomplished?
Here's one approach you may test out:
=arrayformula(let(Σ,A2:index(A:A,match(,0/(A:A<>""))),
Δ,reduce(tocol(,1),unique(eomonth(sequence(F1-E1+1,1,E1),)),lambda(a,c,vstack(a,hstack(let(Λ,date(year(c),month(c),Σ),if(Λ>c,c,Λ)),offset(Σ,,1,,2))))),
filter(Δ,isbetween(choosecols(Δ,1),E1,F1))))