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]

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]

OK, so post 6 does this? (you just have to format the date cells with whatever date format you want)

 

[Skrej]

Another option to consider

*snip

Thank you as well - this works nicely, too. However, I have a similar question about how it works, how to modify for Tue/Thu, etc.

 

[Peter_SSs]

Thank you as well - this works nicely, too. However, I have a similar question about how it works,...

You're welcome.

The red numbers in my formulas below come from this table

1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday

In the second formula, because you were going M/W/F that is always adding 2 days and then an extra day (ie 3 days) if the previous day was a Friday, hence the blue parts of that formula.

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

=IF(B2="","",IF(MONTH(B2+2+(WEEKDAY(B2)=6))=MONTH($A2),B2+2+(WEEKDAY(B2)=6),""))

.. how to modify for Tue/Thu, etc.

Exactly how to modify depends on what is included with the "etc"

 

[Skrej]

You're welcome.

The red numbers in my formulas below come from this table

1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday

In the second formula, because you were going M/W/F that is always adding 2 days and then an extra day (ie 3 days) if the previous day was a Friday, hence the blue parts of that formula.

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

=IF(B2="","",IF(MONTH(B2+2+(WEEKDAY(B2)=6))=MONTH($A2),B2+2+(WEEKDAY(B2)=6),""))



Exactly how to modify depends on what is included with the "etc"

Sorry, I just meant how to adjust for Tue/Thur. I'll play some tomorrow when I have more time and see if I can work out how to adjust for a Tue/Thurs based on your explanation.

However, I've discovered that in Aug and Nov, it starts off with a Saturday. I'll double-check I didn't mess something up when copying the formula for those other months when I get a chance, but Feb, Mar, and June do it as well.

 

[Peter_SSs]

However, I've discovered that in Aug and Nov, it starts off with a Saturday. I'll double-check I didn't mess something up when copying the formula for those other months when I get a chance, but Feb, Mar, and June do it as well.

No, I completely messed that first formula up. I had one working then thought I was making it better, but didn't check carefully enough.
Sorry about that.

Here are the revise MWF versions.
For row 2 I have still included the name of the day above
For the section below I have included the day name in the date formatting so we can see that they are all M/W/F

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Mon	Wed	Fri	Mon	Wed	Fri	Mon	Wed	Fri	Mon	Wed	Fri	Mon
2	15/09/2019	2-Sep-19	4-Sep-19	6-Sep-19	9-Sep-19	11-Sep-19	13-Sep-19	16-Sep-19	18-Sep-19	20-Sep-19	23-Sep-19	25-Sep-19	27-Sep-19	30-Sep-19
3
4	15/10/2019	Wed 2-Oct-19	Fri 4-Oct-19	Mon 7-Oct-19	Wed 9-Oct-19	Fri 11-Oct-19	Mon 14-Oct-19	Wed 16-Oct-19	Fri 18-Oct-19	Mon 21-Oct-19	Wed 23-Oct-19	Fri 25-Oct-19	Mon 28-Oct-19	Wed 30-Oct-19
5	15/11/2019	Fri 1-Nov-19	Mon 4-Nov-19	Wed 6-Nov-19	Fri 8-Nov-19	Mon 11-Nov-19	Wed 13-Nov-19	Fri 15-Nov-19	Mon 18-Nov-19	Wed 20-Nov-19	Fri 22-Nov-19	Mon 25-Nov-19	Wed 27-Nov-19	Fri 29-Nov-19
6	15/12/2019	Mon 2-Dec-19	Wed 4-Dec-19	Fri 6-Dec-19	Mon 9-Dec-19	Wed 11-Dec-19	Fri 13-Dec-19	Mon 16-Dec-19	Wed 18-Dec-19	Fri 20-Dec-19	Mon 23-Dec-19	Wed 25-Dec-19	Fri 27-Dec-19	Mon 30-Dec-19
7	15/01/2020	Wed 1-Jan-20	Fri 3-Jan-20	Mon 6-Jan-20	Wed 8-Jan-20	Fri 10-Jan-20	Mon 13-Jan-20	Wed 15-Jan-20	Fri 17-Jan-20	Mon 20-Jan-20	Wed 22-Jan-20	Fri 24-Jan-20	Mon 27-Jan-20	Wed 29-Jan-20	Fri 31-Jan-20
8	15/02/2020	Mon 3-Feb-20	Wed 5-Feb-20	Fri 7-Feb-20	Mon 10-Feb-20	Wed 12-Feb-20	Fri 14-Feb-20	Mon 17-Feb-20	Wed 19-Feb-20	Fri 21-Feb-20	Mon 24-Feb-20	Wed 26-Feb-20	Fri 28-Feb-20
9	15/03/2020	Mon 2-Mar-20	Wed 4-Mar-20	Fri 6-Mar-20	Mon 9-Mar-20	Wed 11-Mar-20	Fri 13-Mar-20	Mon 16-Mar-20	Wed 18-Mar-20	Fri 20-Mar-20	Mon 23-Mar-20	Wed 25-Mar-20	Fri 27-Mar-20	Mon 30-Mar-20
10	15/04/2020	Wed 1-Apr-20	Fri 3-Apr-20	Mon 6-Apr-20	Wed 8-Apr-20	Fri 10-Apr-20	Mon 13-Apr-20	Wed 15-Apr-20	Fri 17-Apr-20	Mon 20-Apr-20	Wed 22-Apr-20	Fri 24-Apr-20	Mon 27-Apr-20	Wed 29-Apr-20
11	15/05/2020	Fri 1-May-20	Mon 4-May-20	Wed 6-May-20	Fri 8-May-20	Mon 11-May-20	Wed 13-May-20	Fri 15-May-20	Mon 18-May-20	Wed 20-May-20	Fri 22-May-20	Mon 25-May-20	Wed 27-May-20	Fri 29-May-20
12	15/06/2020	Mon 1-Jun-20	Wed 3-Jun-20	Fri 5-Jun-20	Mon 8-Jun-20	Wed 10-Jun-20	Fri 12-Jun-20	Mon 15-Jun-20	Wed 17-Jun-20	Fri 19-Jun-20	Mon 22-Jun-20	Wed 24-Jun-20	Fri 26-Jun-20	Mon 29-Jun-20
13	15/07/2020	Wed 1-Jul-20	Fri 3-Jul-20	Mon 6-Jul-20	Wed 8-Jul-20	Fri 10-Jul-20	Mon 13-Jul-20	Wed 15-Jul-20	Fri 17-Jul-20	Mon 20-Jul-20	Wed 22-Jul-20	Fri 24-Jul-20	Mon 27-Jul-20	Wed 29-Jul-20	Fri 31-Jul-20
14	15/08/2020	Mon 3-Aug-20	Wed 5-Aug-20	Fri 7-Aug-20	Mon 10-Aug-20	Wed 12-Aug-20	Fri 14-Aug-20	Mon 17-Aug-20	Wed 19-Aug-20	Fri 21-Aug-20	Mon 24-Aug-20	Wed 26-Aug-20	Fri 28-Aug-20	Mon 31-Aug-20
List MWF 2

Sorry, I just meant how to adjust for Tue/Thur.

So Tue/Thu would look like this.
Only needs to go across to column K this time.
However, this time we need to add 3 extra days (on top of the normal 2) if the previous date was a Thursday (adding 2 gets to Saturday and then need 3 more to get from there to the following Tuesday).

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1		Tue	Thu	Tue	Thu	Tue	Thu	Tue	Thu
2	15/09/2019	3-Sep-19	5-Sep-19	10-Sep-19	12-Sep-19	17-Sep-19	19-Sep-19	24-Sep-19	26-Sep-19
3
4	15/10/2019	Tue 1-Oct-19	Thu 3-Oct-19	Tue 8-Oct-19	Thu 10-Oct-19	Tue 15-Oct-19	Thu 17-Oct-19	Tue 22-Oct-19	Thu 24-Oct-19	Tue 29-Oct-19	Thu 31-Oct-19
5	15/11/2019	Tue 5-Nov-19	Thu 7-Nov-19	Tue 12-Nov-19	Thu 14-Nov-19	Tue 19-Nov-19	Thu 21-Nov-19	Tue 26-Nov-19	Thu 28-Nov-19
6	15/12/2019	Tue 3-Dec-19	Thu 5-Dec-19	Tue 10-Dec-19	Thu 12-Dec-19	Tue 17-Dec-19	Thu 19-Dec-19	Tue 24-Dec-19	Thu 26-Dec-19	Tue 31-Dec-19
7	15/01/2020	Thu 2-Jan-20	Tue 7-Jan-20	Thu 9-Jan-20	Tue 14-Jan-20	Thu 16-Jan-20	Tue 21-Jan-20	Thu 23-Jan-20	Tue 28-Jan-20	Thu 30-Jan-20
8	15/02/2020	Tue 4-Feb-20	Thu 6-Feb-20	Tue 11-Feb-20	Thu 13-Feb-20	Tue 18-Feb-20	Thu 20-Feb-20	Tue 25-Feb-20	Thu 27-Feb-20
9	15/03/2020	Tue 3-Mar-20	Thu 5-Mar-20	Tue 10-Mar-20	Thu 12-Mar-20	Tue 17-Mar-20	Thu 19-Mar-20	Tue 24-Mar-20	Thu 26-Mar-20	Tue 31-Mar-20
10	15/04/2020	Thu 2-Apr-20	Tue 7-Apr-20	Thu 9-Apr-20	Tue 14-Apr-20	Thu 16-Apr-20	Tue 21-Apr-20	Thu 23-Apr-20	Tue 28-Apr-20	Thu 30-Apr-20
11	15/05/2020	Tue 5-May-20	Thu 7-May-20	Tue 12-May-20	Thu 14-May-20	Tue 19-May-20	Thu 21-May-20	Tue 26-May-20	Thu 28-May-20
12	15/06/2020	Tue 2-Jun-20	Thu 4-Jun-20	Tue 9-Jun-20	Thu 11-Jun-20	Tue 16-Jun-20	Thu 18-Jun-20	Tue 23-Jun-20	Thu 25-Jun-20	Tue 30-Jun-20
13	15/07/2020	Thu 2-Jul-20	Tue 7-Jul-20	Thu 9-Jul-20	Tue 14-Jul-20	Thu 16-Jul-20	Tue 21-Jul-20	Thu 23-Jul-20	Tue 28-Jul-20	Thu 30-Jul-20
14	15/08/2020	Tue 4-Aug-20	Thu 6-Aug-20	Tue 11-Aug-20	Thu 13-Aug-20	Tue 18-Aug-20	Thu 20-Aug-20	Tue 25-Aug-20	Thu 27-Aug-20
List TueThu

 

[Tetra201]

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.

Yes, those binary looking strings represent days of the week with the 0's being class days (workdays) and 1's being days-to-skip (non-work days). The first digit in the string represents Monday, second -- Tuesday, etc. You can get more info on the WORKDAY.INTL function from the Excel help.

The suggested formula is easy to modify for Tue/Thu classes -- you just need to replace both instances of the M/W/F set {""0111111","0101111","0101011""} with a Tue/Thu set {"1011111","1010111"}.

Hope this helps.

 

[Skrej]

Since I didn't quite like having to have a date in A2 due to layout preferences, I modified Peter's formulas a bit to just grab the date from the month/year cells I already will have on the sheet.

I imagine there's a better way to do it, but this works for me: I replaced every reference to A2 with a date function. Cell A6 is a month name, A5 a year


[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]cell[/TD]
[TD]formula[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]=MIN(DATE($A$5,MONTH(DATEVALUE($A$6&" 1")),1)-DAY(DATE($A$5,MONTH(DATEVALUE($A$6&" 1")),1))+8-WEEKDAY(DATE($A$5,MONTH(DATEVALUE($A$6&" 1")),1)-DAY(DATE($A$5,MONTH(DATEVALUE($A$6&" 1")),1))+1-{2,4,6}))[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]=IF(B2="","",IF(MONTH(B2+2+(WEEKDAY(B2)=6))=MONTH(DATE($A$5,MONTH(DATEVALUE($A$6&" 1")),1)),B2+2+(WEEKDAY(B2)=6),""))[/TD]
[/TR]
</tbody>[/TABLE]



Thanks so much to everyone for their suggestions and explanations.

 

[Rick Rothstein]

Since you have the month and year in separate cells, why aren't you using the simpler formula I posted in Message #3 for the formula in cell B2?

 

[Peter_SSs]

Since you have the month and year in separate cells, why aren't you using the simpler formula I posted in Message #3 for the formula in cell B2?

Probably because of this ..

Cell A6 is a month name

@ Skrej
The formulas can be a lot simpler than your modifications.

Excel Workbook
	A	B	C	D	E
1		Mon	Wed	Fri	Mon
2		2-Sep-19	4-Sep-19	6-Sep-19	9-Sep-19
3
4
5	2019
6	September
List MWF 3

Excel Workbook
	A	B	C	D	E
1		Tue	Thu	Tue	Thu
2		3-Sep-19	5-Sep-19	10-Sep-19	12-Sep-19
3
4
5	2019
6	September
List TueThu 2