google-sheets

Create list of dates in Google Sheets based on day-of-month schedule


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?


Solution

  • 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))))
    

    enter image description here