Interesting Week() Behaviour

[Anonymous]

I'm seeing some strange results when using the Week() function when FirstWeekDay=0. When FirstWeekDay=6, things appear normal, but changing it to 0 produces something I don't understand.

E.g. if I use this as an expression (when FirstWeekDay=0):

Week(Date#('12/31/2016', 'M/D/YYYY'))

& '|' &

Week(Date#('1/1/2017', 'M/D/YYYY'))

& '|' &

Week(Date#('1/2/2017', 'M/D/YYYY'))

The result is:

53 | 1 | 2

I understand that there were 53 ISO weeks last year, but how could Jan. 1st of this year be week 1, and Jan. 2 be week 2? Shouldn't Jan 1st still be in week 53 and Jan 2nd be in week 1?

[sunny_talwar]

Not sure I understand the behavior, but WeekName gives this...

=WeekName(Date#('12/31/2016', 'M/D/YYYY'))

& ' | ' &

WeekName(Date#('1/1/2017', 'M/D/YYYY'))

& ' | ' &

WeekName(Date#('1/2/2017', 'M/D/YYYY'))

[annapochyla · Accepted solution]

There is one more system variable you should take into consideration:

SET BrokenWeeks=0;

Default value is 0, but if you will change it to 1, then you will get this unexpected result.

1 means that for each year you start the weeks numeration from the beginning.

[Anonymous]

Thanks Anna, that corrects my issue. My default BrokenWeeks is one for some reason, but changing to 0 gives the results I was expecting.