Counting weekdays between two dates

heidigray 

You only need to include the question about the calendar week in your formula.

The WEEKNUM() function cannot represent an array, so I have packed this into INDEX() and SEQUENCE(). Of course, it also works with BYROW() if you already have that available. The search of the date in A1 can also be achieved with TEXTAFTER(TEXTBEFORE()) or TEXTSPLIT().

Please insert the formula in cell D2 and then copy it down.

=SUMPRODUCT(--(WEEKDAY(D$11:D$37,2)=C2),--(WEEKNUM(INDEX(D$11:D$37,SEQUENCE(COUNT(D$11:D$37))))=WEEKNUM(MID(A$1,FIND("s ",A$1)+2,(FIND("-",A$1))-(FIND("s ",A$1)+2)))))

alternative:

=SUMPRODUCT(--(WEEKDAY(D$11:D$37,2)=C2),--(BYROW(D$11:D$37,LAMBDA(in,WEEKNUM(in)))=WEEKNUM(MID(A$1,FIND("s ",A$1)+2,(FIND("-",A$1))-(FIND("s ",A$1)+2)))))

alternative:

=SUMPRODUCT(--(WEEKDAY(D$11:D$37,2)=C2),--(WEEKNUM(INDEX(D$11:D$37,SEQUENCE(COUNT(D$11:D$37))))=WEEKNUM(TEXTBEFORE(TEXTAFTER(A$1," ",2),"-"))))

alternative:

=SUMPRODUCT(--(WEEKDAY(D$11:D$37,2)=C2),--(WEEKNUM(INDEX(D$11:D$37,SEQUENCE(COUNT(D$11:D$37))))=WEEKNUM(INDEX(TEXTSPLIT(A$1,{" ";"-"}),3))))