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 |
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?
Share Improve this question edited Nov 20, 2024 at 17:54 kraftydevil asked Nov 20, 2024 at 17:30 kraftydevilkraftydevil 5,2466 gold badges45 silver badges66 bronze badges 2- 5 Do NOT share spreadsheets/images as the only source of data, to avoid closure of the question. Make sure to add input and expected output as a plain text table to the question. Click here to create a markdown table, which is easier to copy/paste as well. Also, note that your email address can also be accessed by the public, if you share Google files. – Jats PPG Commented Nov 20, 2024 at 17:42
- 2 Updated with actual text. Great resource, thanks – kraftydevil Commented Nov 20, 2024 at 17:48
1 Answer
Reset to default 1Here'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))))