Weeknum(Today())

[Jaros]

My weeknum today is expression is giving me a result of 26 but when I check Google the result is 25.

What could be the reason for the difference?

[Aurelien]

Hi @Jaros

I got the exact same issue a few weeks earlier.
I guess you are in Europe ?

We have a different week counting system for weeks in Europe, compared to USA.
There is no way to set a parameter to explicitly change that, other than adding “-1” to weeknum.

More details here:

Shift in WEEKNUM() calculation? Questions

Ok, so after reading the post you provide (thanks again !), I understand this is a matter of ISO calendar. Investigating on the Sheets side gave the result: there is an additional parameter. WEEKNUM(date, [type]), where [type] is an optional parameter. [image] @Steve, do you know if some update on WEEKNUM expression is planned, or into further developments of locale settings ?

[khuslid]

For now, there is no way around, but to hard code…
You could add a virtual column like CONCATENATE(20211202202023020241,etc.) and read the week number like
WEEKNUM([DATE])+LEFT(INDEX(SPLIT([WeekNumber],YEAR([DATE])),2),1)

What I heard, ISO WEEKNUM is in the pipeline of Appsheet…

[Aurelien]

I would LOVE the isoweeknum

[Jaros]

Will this work going forward into a new year? Or do you foresee issues?

[Aurelien]

No idea.
Depending on your use case, you can either:

• choose to come to terms with it
• calculate a minus 1, if year is 2021 and 2022 at least

That’s not really satisfying. Hopefully, there will be sometime the possiblity to pick the counting system in the locale, or to have the locale taken into account for weeknum calculation.

Strictly speaking, this is not an issue, just a matter of displaying.
As long as your users are aware of the counting system, this is not an issue

[Jaros]

Thanks Aurelien

[khuslid]

Will this work going forward into a new year? Or do you foresee issues?:

As long as you hard code the coming years with 52 or 53 week in the 20211202202023020241,etc., it will work. (For as many years you put into it).

[Aurelien]

that’s a good trick.
another trick would be to calculate if the first day of year is before or after a thursday, which is the difference between US system and EU system on week counting.
then elaborate an expression based on it. Nonetheless, if ISONUMWEEK is on the pipeline, then you solution seems great

[Jaros]

Thanks. I would love the ISOWEEKNUM too

[Aleksi]

If interested for a workaround… this should work.

FLOOR(
(10 +
IF(
MOD(YEAR(TODAY()),4) = 0,
INDEX({0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335},MONTH(TODAY())),
INDEX({0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334},MONTH(TODAY()))
) + DAY(TODAY()) - (WEEKDAY(TODAY()) - 1)) / 7
)

[Jaros]

Aleksi:

FLOOR(
(10 +
IF(
MOD(YEAR(TODAY()),4) = 0,
INDEX({0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335},MONTH(TODAY())),
INDEX({0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334},MONTH(TODAY()))
) + DAY(TODAY()) - (WEEKDAY(TODAY()) - 1)) / 7
)

Thanks!

[Ratatosk]

I just discovered that ISOWEEKNUM works!

[Aurelien]

yes, whole new expression