Jan 18 2023 09:00 AM
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!
Jan 18 2023 02:45 PM
SolutionYou 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))))
Jan 18 2023 03:47 PM
Jan 18 2023 02:45 PM
SolutionYou 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))))