Adapting calendar to only show Mon-Thurs of a month

[Skrej]

I'm trying to create an attendance roster that will automatically populate as I change the month and year. However, to save space, I only want it to calculate said dates for M-Th (days that we have class).

I'm trying to adapt some previous help I received.

Cell B8 has the following formula to calculate the first weekday of the month:

=EOMONTH($A$1,-1)+1+CHOOSE(WEEKDAY(EOMONTH($A$1,-1)+1,2),0,0,0,0,3,2,1)

Cell B9 calculates the next weekday:

=IF(CHOOSE(WEEKDAY(B8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Thu",B8+4,IF(CHOOSE(WEEKDAY(B8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Fri",B8+3,IF(CHOOSE(WEEKDAY(B8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Sat",B8+2,IF(CHOOSE(WEEKDAY(B8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Sun",B8+4,B8+1))))

This is copied across to cell W8. Of course, depending on how the calendar falls, I frequently end up with several days in the next month.

I would like to stop calculating dates if the next weekday will be in the next month (or possibly just write an empty string if it's into the next month).

However, with the columns being dynamic, I'm not sure how to reference the last calculated date to see if it's into the next month.

Another calendar I use for other purposes calculates the first day of the month in cell c1 with this formula:

=DATE(AB3,@INDEX({1,2,3,4,5,6,7,8,9,10,11,12},MATCH(AB2,monthNames,0)),1)

.

From cells c2-C29,

=C7+1

is copied across to simply increase the date by one day, up until you hit the 28th day, at which point

=IF(MONTH($AD7+1)>MONTH($C$7),"",$AD7+1)

writes blank cells if the date is in the next month.

However, since that calendar takes into account every day of the month, the columns are static until the 28th day. I'm just unclear on how to reference the previous cell in a row when I can't predict what the column will be.

I've tried nesting another if statement such as

=IF(CHOOSE(WEEKDAY(P8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Thu",P8+4,IF(CHOOSE(WEEKDAY(P8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Fri",P8+3,IF(CHOOSE(WEEKDAY(P8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Sat",P8+2,IF(CHOOSE(WEEKDAY(P8),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")="Sun",P8+4,P8+1)))), IF(MONTH($a3+1)>MONTH($B$8),"",$a3+1)

but I run into value errors and circular reference errors.

So, in a nutshell, the calendar should start on the first non-Friday weekday of the month, skip over all subsequent Fridays-Sundays, and stop on the last non-Friday weekday.

I'd appreciate any suggestions.

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	11/1/2021
2
3
4
5
6
7
8		11/1/2021	11/2/2021	11/3/2021	11/4/2021	11/8/2021	11/9/2021	11/10/2021	11/11/2021	11/15/2021	11/16/2021	11/17/2021	11/18/2021	11/22/2021	11/23/2021	11/24/2021	11/25/2021	11/29/2021	11/30/2021
Sheet9

Cell Formulas
Range	Formula
B8	B8	=WORKDAY.INTL(A1-1,1,"0000111")
C8:W8	C8	=IFERROR(IF(WORKDAY.INTL(B8,1,"0000111")>EOMONTH($A$1,0),"",WORKDAY.INTL(B8,1,"0000111")),"")

 

[Skrej]

Thank you, that's certainly much tidier than the nested IF functions. It did the trick.

Just for the record, I added the following in A:7:T7 to calculate the day of the week.

=IF(B12<>"",CHOOSE(WEEKDAY(B$12),"Sun","Mon","Tue","Wed","Thu","Fri","Sat"),"")

 

[Eric W]

Glad I could help!

And your formula for the day of the week works fine, but here's an alternate one that's a bit shorter:

=TEXT(B8,"ddd")