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:

Mon	Wed	Friday	Mon	Wed	Friday	Mon	Wed	Fri
9/2	9/4	9/6	9/9	9/11	9/13	9/16	9/18	9/20

<tbody>
</tbody>

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

Wed

Fri

Mon

Wed

Fri

Mon

Wed

Fri

Mon

Wed

Fri

Mon

Wed

10/2

10/4

10/7

10/9

10/11

10/14

10/16

10/18

10/21

10/23

10/25

10/28

10/30

<tbody>
</tbody>

 

[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.