Custome Week Numbers

Hi I have multiple dates spanning back a number of years and forward a number of years.

For Context, I need the following.

The week will start on a Friday and end on a Thursday. Week 1 will be on 01st June regardless of the day it starts and week 2 will start on the next Friday (example here is 2023 01st June was a Thursday so this was week 1 then week 2 began the following day as we hit a Friday so a new week began)

For 2022 I used the following =IF(YEAR(DATE(YEAR(F2),MONTH(F2)-5,1))=2022,WEEKNUM(F2-151,10+WEEKDAY(DATE(YEAR(F2-151),4,1),15)) however this does not work for any year outside 2022.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

In G2 copied down:

=LET(s,SEQUENCE(20,,-1,12),d,EOMONTH(F2,s)+1,w,WORKDAY.INTL(EOMONTH(d,-1)+1,1,"1111011"),h,HSTACK(d,w),v,VSTACK(HSTACK(VLOOKUP(F2,h,1,1),1),HSTACK(SEQUENCE(52,,VLOOKUP(DATE(YEAR(F2),6,1),h,2,1),7),SEQUENCE(52,,2,1))),VLOOKUP(F2,v,2,1))

This will work from 2022 to 2041.

Let me look again.

In G2 copied down:

Please Login or Register  to view this content.

Still not quite right - looking again.

This formula works for every year, please try

Formula:

Please Login or Register  to view this content.

And if you don't want to copy down the formula, please try this for everything in one go:

Please empty cells G2:G26 and try in G2:

Formula:

Please Login or Register  to view this content.

What date range will this work for?

1 June 1900 until 31 December 9999

Thanks for the feedback and rep . Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.