Week Number

[vmjan02]

I have this date in excel not sure the formula is not giving me correct output. any idea

in cell B2 the formula is

=WEEKNUM(MAX($A:$A),1)

in cells D14 to D2 formulas are

[TABLE]
[TR]
[TD]=WEEKNUM(MAX(A:A),1)-C2[/TD]
[/TR]
[TR]
[TD]=WEEKNUM(MAX(A:A),1)-C3[/TD]
[/TR]
[TR]
[TD]=WEEKNUM(MAX(A:A),1)-C4[/TD]
[/TR]
[TR]
[TD]=WEEKNUM(MAX(A:A),1)-C5[/TD]
[/TR]
[TR]
[TD]=WEEKNUM(MAX(A:A),1)-C6[/TD]
[/TR]
[TR]
[TD]=WEEKNUM(MAX(A:A),1)-C7[/TD]
[/TR]
[TR]
[TD]=WEEKNUM(MAX(A:A),1)-C8[/TD]
[/TR]
[TR]
[TD]=WEEKNUM(MAX(A:A),1)-C9[/TD]
[/TR]
[TR]
[TD]=WEEKNUM(MAX(A:A),1)-C10[/TD]
[/TR]
[TR]
[TD]=WEEKNUM(MAX(A:A),1)-C11[/TD]
[/TR]
[TR]
[TD]=WEEKNUM(MAX(A:A),1)-C12[/TD]
[/TR]
[TR]
[TD]=WEEKNUM(MAX(A:A),1)-C13[/TD]
[/TR]
[TR]
[TD]=WEEKNUM(MAX($A:$A),1)[/TD]
[/TR]
[/TABLE]

date	Week Max		Pervious 12 Weeks
15-May-23​	11​	12​	-1​	Output should be 51
22-May-23​		11​	0​	Output should be 52
29-May-23​		10​	1​
5-Jun-23​		9​	2​
12-Jun-23​		8​	3​
19-Jun-23​		7​	4​
26-Jun-23​		6​	5​
3-Jul-23​		5​	6​
10-Jul-23​		4​	7​
17-Jul-23​		3​	8​
24-Jul-23​		2​	9​
31-Jul-23​		1​	10​
7-Aug-23​		Current Week	11​
14-Aug-23​
21-Aug-23​
28-Aug-23​
4-Sep-23​
11-Sep-23​
18-Sep-23​
25-Sep-23​
2-Oct-23​
9-Oct-23​
16-Oct-23​
23-Oct-23​
30-Oct-23​
6-Nov-23​
13-Nov-23​
20-Nov-23​
27-Nov-23​
4-Dec-23​
18-Dec-23​
11-Dec-23​
25-Dec-23​
1-Jan-24​
8-Jan-24​
15-Jan-24​
22-Jan-24​
29-Jan-24​
5-Feb-24​
12-Feb-24​
19-Feb-24​
26-Feb-24​
4-Mar-24​
11-Mar-24​

 

[bobsan42]

the formula output is correct
MAX(A:A) is 11-Mar-24 -> WeekNum((11-03-2024),1) is 11
then 0-1 is -1 so the formulas output is also correct, just not what you need
for the previous week numbers, try this:
=WEEKNUM(MAX(A:A)-C2*7,1)

 

[vmjan02]

the formula output is correct
MAX(A:A) is 11-Mar-24 -> WeekNum((11-03-2024),1) is 11
then 0-1 is -1 so the formulas output is also correct, just not what you need
for the previous week numbers, try this:
=WEEKNUM(MAX(A:A)-C2*7,1)

thanks just a small think, just missed from my mind. thanks man