finding first MWF of week

[Skrej]

I'm trying to figure out a formula for an attendance roster that will calculate the first M/W/F of a month. Since this particular class only meets M/W/F, I'd like to generate an attendance roster that for each month (each month on separate sheet) which finds the first day of the month that's either a M/W/F, then calculate all the subsequent M/W/F days, skipping over weekends, etc. The in the row above, calculate which day of the week that date is

Here's a partially completed example of what September 2019 would look like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Mon[/TD]
[TD]Wed[/TD]
[TD]Friday[/TD]
[TD]Mon[/TD]
[TD]Wed[/TD]
[TD]Friday[/TD]
[TD]Mon[/TD]
[TD]Wed[/TD]
[TD]Fri[/TD]
[/TR]
[TR]
[TD]9/2[/TD]
[TD]9/4[/TD]
[TD]9/6[/TD]
[TD]9/9[/TD]
[TD]9/11[/TD]
[TD]9/13[/TD]
[TD]9/16[/TD]
[TD]9/18[/TD]
[TD]9/20[/TD]
[/TR]
</tbody>[/TABLE]

I've been playing with some formulas I've googled, but can't quite seem to get them pieced together.

thanks in advance

 

[Peter_SSs]

What would the full row for October 2019 look like?

 

[Rick Rothstein]

...finds the first day of the month that's either a M/W/F,

The following array-entered** formula should give you the date of the first day of the month that is either a Monday, Wednesday or Friday...

=MIN(DATE(A1,B1,8)-WEEKDAY(DATE(A1,B1,8-{2,4,6})))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[Tetra201]

Let's assume you have a date (say, 9/1/2019), in cell A1.

Put the following formula in cell A4 and drag-copy it until you reach cell N4:

=IF(ISERR(1/(MIN(WORKDAY.INTL(EOMONTH($A$1,-1),COLUMNS($A$1:A1),{"0111111","0101111","0101011"}))<=EOMONTH($A$1,0))),"",MIN(WORKDAY.INTL(EOMONTH($A$1,-1),COLUMNS($A$1:A1),{"0111111","0101111","0101011"})))

Custom-format the fourteen cells with the formulas as m/d

Then, put the following formula in cell A3 and drag-copy it until you reach cell N3:

=TEXT(A4,"ddd")

 

[Tetra201]

Update: here is a simpler formula for cell A4:

=IF(MIN(WORKDAY.INTL(EOMONTH($A$1,-1),COLUMNS($A$1:A1),{"0111111","0101111","0101011"}))>EOMONTH($A$1,0),"",MIN(WORKDAY.INTL(EOMONTH($A$1,-1),COLUMNS($A$1:A1),{"0111111","0101111","0101011"})))

 

[Peter_SSs]

Another option to consider

Fist of month in A2
B1 copied across to O1
B2 stand-alone formula
C2 copied across to O2

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Wed	Fri	Mon	Wed	Fri	Mon	Wed	Fri	Mon	Wed	Fri	Mon	Wed
2	1/10/2019	2-Oct-19	4-Oct-19	7-Oct-19	9-Oct-19	11-Oct-19	14-Oct-19	16-Oct-19	18-Oct-19	21-Oct-19	23-Oct-19	25-Oct-19	28-Oct-19	30-Oct-19
List MWF

If you want to be able to put ANY date in A2, then just change B2 to

=A2-DAY(A2)+1+AGGREGATE(15,6,7-WEEKDAY(A2-DAY(A2)+2+{2,4,6}),1)

 

[Skrej]

What would the full row for October 2019 look like?

It would look like this (if I've read my calendar correctly...)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Wed
[/TD]
[TD]Fri
[/TD]
[TD]Mon
[/TD]
[TD]Wed
[/TD]
[TD]Fri
[/TD]
[TD]Mon
[/TD]
[TD]Wed
[/TD]
[TD]Fri
[/TD]
[TD]Mon
[/TD]
[TD]Wed
[/TD]
[TD]Fri
[/TD]
[TD]Mon
[/TD]
[TD]Wed
[/TD]
[/TR]
[TR]
[TD]10/2
[/TD]
[TD]10/4
[/TD]
[TD]10/7
[/TD]
[TD]10/9
[/TD]
[TD]10/11
[/TD]
[TD]10/14
[/TD]
[TD]10/16
[/TD]
[TD]10/18
[/TD]
[TD]10/21
[/TD]
[TD]10/23
[/TD]
[TD]10/25
[/TD]
[TD]10/28
[/TD]
[TD]10/30
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Skrej]

The following array-entered** formula should give you the date of the first day of the month that is either a Monday, Wednesday or Friday...

=MIN(DATE(A1,B1,8)-WEEKDAY(DATE(A1,B1,8-{2,4,6})))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

I'm guessing I need a date in A1, but what would be in B1?

 

[Rick Rothstein]

I'm guessing I need a date in A1, but what would be in B1?

Sorry, I should have mention it... A1 is the year, B1 is the month number.

 

[Skrej]

Update: here is a simpler formula for cell A4:

=IF(MIN(WORKDAY.INTL(EOMONTH($A$1,-1),COLUMNS($A$1:A1),{"0111111","0101111","0101011"}))>EOMONTH($A$1,0),"",MIN(WORKDAY.INTL(EOMONTH($A$1,-1),COLUMNS($A$1:A1),{"0111111","0101111","0101011"})))

Thanks, this does the trick. However, I can't follow what's going on. Could you (or somebody else) sort of step through it, so I can see how to modify it for future use? For example, modify it for Tue/Thu classes. I'm guessing some of those binary looking numbers represent the days of the week.