Formula to Get week Number starting on Wednesday

[drom]

Hi and thanks in advance!

In our company the week number goes from Wednesday to Tuesday

Check the Following table:

Row/Col	A	B	C
1	DATE	Desired WeekNumber	Formula?
2	2020-Dec-23 Wednesday	52
3	2020-Dec-24 Thursday	52
4	2020-Dec-25 Friday	52
5	2020-Dec-26 Saturday	52
6	2020-Dec-27 Sunday	52
7	2020-Dec-28 Monday	52
8	2020-Dec-29 Tuesday	52
9	2020-Dec-30 Wednesday	53
10	2020-Dec-31 Thursday	53
11	2021-Jan-01 Friday	53
12	2021-Jan-02 Saturday	53
13	2021-Jan-03 Sunday	53
14	2021-Jan-04 Monday	53
15	2021-Jan-05 Tuesday	53
16	2021-Jan-06 Wednesday	1
17	2021-Jan-07 Thursday	1

I would like to have a formula to get the Week number I put manually in Column B
I have been trying

• =WEEKNUM($A2, 1)
• =WEEKNUM($A2, 2)
• =WEEKNUM($A2, 11)
• =WEEKNUM($A2, 12)
• =WEEKNUM($A2, 13)
• =WEEKNUM($A2, 14)
• =WEEKNUM($A2, 15)
• =WEEKNUM($A2, 16)
• =WEEKNUM($A2, 17)
• =WEEKNUM($A2, 21)
• =ISOWEEKNUM($A2)

But non of those formulas works

• from the 2021-Jan-04 Monday

Thanks again

 

[JamesCanale]

MrExcelPlayground2.xlsx
	A	B	C
1	DATE	Desired WeekNumber	Formula
2	Wednesday, December 23, 2020	52	52
3	Thursday, December 24, 2020	52	52
4	Friday, December 25, 2020	52	52
5	Saturday, December 26, 2020	52	52
6	Sunday, December 27, 2020	52	52
7	Monday, December 28, 2020	52	52
8	Tuesday, December 29, 2020	52	52
9	Wednesday, December 30, 2020	53	53
10	Thursday, December 31, 2020	53	53
11	Friday, January 1, 2021	53	53
12	Saturday, January 2, 2021	53	53
13	Sunday, January 3, 2021	53	53
14	Monday, January 4, 2021	53	53
15	Tuesday, January 5, 2021	53	53
16	Wednesday, January 6, 2021	1	1
17	Thursday, January 7, 2021	1	1
18	Friday, January 8, 2021		1
19	Saturday, January 9, 2021		1
20	Sunday, January 10, 2021		1
21	Monday, January 11, 2021		1
22	Tuesday, January 12, 2021		1
23	Wednesday, January 13, 2021		2
24	Thursday, January 14, 2021		2
25	Friday, January 15, 2021		2
Sheet22

Cell Formulas
Range	Formula
C2:C25	C2	=WEEKNUM(INT((A2-4)/7)*7+4,21)