ISO WEEK NUMBER FORMULA

[VBA learner ITG]

Hi all,

I have a date which is 31/12/2019 and when i use this formula : =WEEKNUM(E8435,21)

its returning week 1 2019

My question is why doesnt it show week 52 2019

Any help is appreciated.

 

[RoryA]

WEEKNUM doesn't return a year, just a week number. I think you'll find it's week 1 2020, since that week contains the first Thursday of 2020.

 

[VBA learner ITG]

If i use the below syntax it shows it correctly.

=WEEKNUM(E8435,2)& TEXT(E8435," - yyyy")

"Week 53 2019"

But if i use

=WEEKNUM(E8435,21)& TEXT(E8435," - yyyy")

It appears as week 1 2019 intead of the above!

Below taken from Microsoft website to use for start of the week day.

1 or omitted	Sunday	1
2	Monday	1
11	Monday	1
12	Tuesday	1
13	Wednesday	1
14	Thursday	1
15	Friday	1
16	Saturday	1
17	Sunday	1
21	Monday	2

 

[RoryA]

If you read the full help file, you'll note that the 21 option treats the week with the first Thursday of the year as week 1. Since the week containing 31/12/2019 has the first Thursday of 2020, it is week one. Your TEXT formula simply takes the year of the passed date with no further considerations. Why are you using 21 as the argument?