Get the last day of the week from a week number

Question

I'm succesfully using the formula provided here in an answer here: Get date from week number in Google Sheets

So, I have:

=DATE(2023,1,1)-WEEKDAY(DATE(2023,1,1),3)+7*(WEEKDAY(DATE(2023,1,1),3)>3)+7*(C2-1)

Where C2 is a cell with the week number, and I'm getting the expected result 02/01/2023.

What do I need to change to get the result of 08/01/2023? - the last date of the week?

Thank you again for all of your help!

Part of my problem is probably that I can't work out what the bold parts refer to/mean/do:

=DATE(2023,1,1)-WEEKDAY(DATE(2023,1,1),3)+7*(WEEKDAY(DATE(2023,1,1),3)>3)+7*(C2-1)

Answer 1

To get a date that is 6 days later than the formula result, add 6 to that result, like this:

=6 + date(2023, 1, 1) - weekday(date(2023, 1, 1), 3) + 7 * (weekday(date(2023, 1, 1), 3) > 3) + 7 * (C2 - 1)

See Working with date and time values in Google Sheets.

Your formula looks unnecessarily complex. It is likely that there are much easier ways to get those dates, depending on your requirements.

Answer 2

you can try:

=LAMBDA(aix,MAX(FILTER(aix,WEEKNUM(aix,21)=C2)))(SEQUENCE(365,1,DATE(2023,1,1),1))