admin管理员组文章数量:1332716
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))))
本文标签： Create list of dates in Google Sheets based on dayofmonth scheduleStack Overflow
版权声明：本文标题：Create list of dates in Google Sheets based on day-of-month schedule - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1742340972a2456595.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论