Custom Week Number

[AlmostBeginner19]

If anyone could help on this, it would be greatly appreciated.
I thought this was gonna be just a simple WEEKNUM formula but it's giving me quite a hard time.

I have a column of dates (column A) and I want to get the week number based on a custom week.
The week starts from Thursday to Wednesday.
The year starts on January 2, 2020 so week 1 is from Jan 2 to Jan 8, 2020.
The last week of the year is Dec 31, 2020 to Jan 6, 2021, then the week number resets to 1.

I tried numerous other formulas but couldn't get it to work.
The last formula I tried is: =INT((A4-DATE(YEAR(A4+364)-1,1,2)-WEEKDAY(A4-1,12))/7)+2
Based on a formula I got from another forum. This is actually not the original formula, I tweaked it, trying to experiment in different ways, but still didn't work.

Thanks to anyone who would help.

 

[DanteAmor]

Try this:

Dante Amor
	A	B
1	miércoles, 01 de enero de 2020	53
2	jueves, 02 de enero de 2020	1
3	viernes, 03 de enero de 2020	1
4	sábado, 04 de enero de 2020	1
5	domingo, 05 de enero de 2020	1
6	lunes, 06 de enero de 2020	1
7	martes, 07 de enero de 2020	1
8	miércoles, 08 de enero de 2020	1
9	jueves, 09 de enero de 2020	2
10	viernes, 10 de enero de 2020	2
11	sábado, 11 de enero de 2020	2
12	domingo, 12 de enero de 2020	2
13	lunes, 13 de enero de 2020	2
14	martes, 14 de enero de 2020	2
15	miércoles, 15 de enero de 2020	2
16	jueves, 16 de enero de 2020	3
17	viernes, 17 de enero de 2020	3
18	sábado, 18 de enero de 2020	3
19	domingo, 19 de enero de 2020	3
20	lunes, 20 de enero de 2020	3
21	martes, 21 de enero de 2020	3
22	miércoles, 22 de enero de 2020	3
23	jueves, 23 de enero de 2020	4
Hoja3

Cell Formulas
Range	Formula
B1:B23	B1	=INT((A1+4-DATE(YEAR(A1+365),-11,-1))/7)

 

[AlmostBeginner19]

Got it. Thanks soooo much!
Though would it be possible to explain how you got this? Like why the +4, -11, and -1?
I just want to understand how this works so I could do it myself in the future.

Try this:

Dante Amor
	A	B
1	miércoles, 01 de enero de 2020	53
2	jueves, 02 de enero de 2020	1
3	viernes, 03 de enero de 2020	1
4	sábado, 04 de enero de 2020	1
5	domingo, 05 de enero de 2020	1
6	lunes, 06 de enero de 2020	1
7	martes, 07 de enero de 2020	1
8	miércoles, 08 de enero de 2020	1
9	jueves, 09 de enero de 2020	2
10	viernes, 10 de enero de 2020	2
11	sábado, 11 de enero de 2020	2
12	domingo, 12 de enero de 2020	2
13	lunes, 13 de enero de 2020	2
14	martes, 14 de enero de 2020	2
15	miércoles, 15 de enero de 2020	2
16	jueves, 16 de enero de 2020	3
17	viernes, 17 de enero de 2020	3
18	sábado, 18 de enero de 2020	3
19	domingo, 19 de enero de 2020	3
20	lunes, 20 de enero de 2020	3
21	martes, 21 de enero de 2020	3
22	miércoles, 22 de enero de 2020	3
23	jueves, 23 de enero de 2020	4
Hoja3

Cell Formulas
Range	Formula
B1:B23	B1	=INT((A1+4-DATE(YEAR(A1+365),-11,-1))/7)

 

[Tetra201]

... The last week of the year is Dec 31, 2020 to Jan 6, 2021, then the week number resets to 1...

DanteAmor's formula doesn't seem to handle this condition correctly.

Here is my take on this:

=WEEKNUM(WORKDAY.INTL(A4+1,-1,"1110111"),21)

 

[DanteAmor]

The last week of the year is Dec 31, 2020 to Jan 6, 2021, then the week number resets to 1.

Sorry, I skipped that part. I checked the formula and now it is. It also calculates if the year will end with 52 or 53 weeks.

Formula for date in cell A4

=IF(INT((A4+CHOOSE(WEEKDAY(DATE(YEAR(A4),1,1),1),2,3,4,5,6,0,1)-DATE(YEAR(A4)-1,12,31))/7)=0,IF(WEEKDAY(DATE(YEAR(A4),1,1),1)=6,53,52),INT((A4+CHOOSE(WEEKDAY(DATE(YEAR(A4),1,1),1),2,3,4,5,6,0,1)-DATE(YEAR(A4)-1,12,31))/7))

 

[AlmostBeginner19]

Thanks. It worked but I'm curious to know how the "1110111" part works. When you select a [weekend] it's only 1 to 17.

DanteAmor's formula doesn't seem to handle this condition correctly.

Here is my take on this:

=WEEKNUM(WORKDAY.INTL(A4+1,-1,"1110111"),21)

 

[AlmostBeginner19]

This one worked as well. Thanks.

Sorry, I skipped that part. I checked the formula and now it is. It also calculates if the year will end with 52 or 53 weeks.

Formula for date in cell A4

=IF(INT((A4+CHOOSE(WEEKDAY(DATE(YEAR(A4),1,1),1),2,3,4,5,6,0,1)-DATE(YEAR(A4)-1,12,31))/7)=0,IF(WEEKDAY(DATE(YEAR(A4),1,1),1)=6,53,52),INT((A4+CHOOSE(WEEKDAY(DATE(YEAR(A4),1,1),1),2,3,4,5,6,0,1)-DATE(YEAR(A4)-1,12,31))/7))

 

[Tetra201]

@ AlmostBeginner19:

Glad it worked for you.

The "1110111" declares Thursday as the only workday; all other days (Mon, Tue, Wed, Fri, Sat, Sun) are declared as holidays: "1110111".

The WORKDAY.INTL(A4+1,-1,"1110111") returns the date of the previous Thursday for Mon, Tue, Wed, Fri, Sat, Sun.