Counting weekdays between two dates

[heidigray]

Hello all,

I have a spreadsheet that I have made to track my homework on a weekly basis. I have a graph that shows the amount of assignments I have each day. The formula that I found on google that I use to count each week day would seem to work very well for all of the days except Saturday where it registers 13 for some reason when it should be 0, so that is the first thing that I need help with. Additionally I need to find a way to only include dates from that week in the counts. Not all of my assignments are due the same week that they are assigned, so it is misrepresentative to say that there are 5 assignments due on Tuesday of this week, when 3 of them are really do on separate Tuesdays over the next few weeks. I am okay with having to enter the start date and end date of the week again, though it would be awesome if it could just pull that from the title (which I can change to just have the date if need be, as well as include the weekends)

Thank you!

[#2 · Accepted solution]

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

[#3]

Thank you so much!!!

The first alternative formula totally worked! I have no idea why, or what it is doing, but it works! Excel wizardry!!!

Thank you so much!!!!