Populating three non-consecutive days of the week for the month

Question

I'm wanting to find a formula for populating Tuesdays, Thursdays, and Saturdays for the entire month, then also generating their respective dates. The purpose would be to track donations made to our group on those specific days and dates.

Answer 1

This is an old thread! Try:

=LET(aDate,G4,dom,WORKDAY.INTL(EOMONTH(aDate,-1),SEQUENCE(15),"1010101"),FILTER(dom, MONTH(dom)=MONTH(aDate)))

where aDate (cell G4 in the formula above) is any date in the requisite month

or convert it to a named lambda with:

=LAMBDA(aDate,LET(dom,WORKDAY.INTL(EOMONTH(aDate,-1),SEQUENCE(15),"1010101"),FILTER(dom, MONTH(dom)=MONTH(aDate))))

So that you can use it (with argument hints) thus:

Answer 2

I get different answers in D3 for other months, due I belive, to a small logic error.

Answer 3

Hi,

In cell A3, enter this formula

=LET(l,WORKDAY.INTL(A1,SEQUENCE(NETWORKDAYS.INTL(A1-1,EOMONTH(A1,0),"1010101")-1),"1010101"),HSTACK(l,TEXT(l,"dddd")))

In cell A1, type the first date of any month

Answer 4

In an unused area of your workbook, build an array of 7 rows and 4 columns. The first column is set to values 1 through 7 corresponding to the day of the week that the first of the month falls on. The next three columns contain the the number of days to add to the 1st of the month for the first Tuesday, Thursday, and Saturday. Thus the first row would contain 1, 2, 4, and 6 and the seventh row would contain 7, 3, 5, and 0.

In your data area, build an array of 15 rows and two columns:

• Populate the first row of column one with the formula =VLOOKUP(WEEKDAY(1st_of_month_date), the_array_of_offsets_, 2). This is the offset to the first Tuesday of the month.
• Each of the next four rows is the previous row plus 7.
• Populate the sixth row with =VLOOKUP(WEEKDAY(1st_of_month_date), the_array_of_offsets_, 3). This is the offset to the first Thursday.
• The remaining rows should be obvious.
• Populate each row of the second column with =IF(MONTH(1st_of_month_date+this_offset) <> MONTH(1st_of_month_date), 0, 1st_of_month_date+this_offset)

This second column of this data array contains the 12 to 14 dates that correspond to the three weekdays of interest for the month you specify.