Shift in WEEKNUM() calculation?

Aurelien · 05-21-2021 06:10 AM

Hi all,

I noticed something a long time ago, but did not take time to investigate.
Maybe you can enlighten me ?
According to this post:

The expression WEEKNUM() returns the week number, 1 to 53 if parameter is a valid date.

Today, 21st may of 2021, is calculated as week number 21.
If I pick en english-formatted calendar (from : https://www.calendar-365.com/week-number.html), I get week number 20.
My own personal calendar displays number 20 as well.

I cannot explain why this happens, plus my locale is on France/French/Europe, both on Table locale and Sheets locale.

I notice the same output with WEEKNUM() performed in Sheets.

Any explanation welcome

Steve

An earlier thread about this issue:

Aurelien

Oh thanks, I’ve been looking a long time for a thread like this !

Aurelien

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.

@Steve, do you know if some update on WEEKNUM expression is planned, or into further developments of locale settings ?

Steve

I have no insight to share on either. Consider creating a #requests post to request the changes.

Aurelien

Yes, that what I was to about to do
Thanks anyway !

Koichi_Tsuji

It looks like to me that we are able to push our own expression to get the European week number system by validating the 1st Thursday of the year, then run own calcuation for week number to fill the gaps.

Aurelien

Hi @tsuji_koichi,

You are right indeed.

My issue was more specifically pointing the week automatically calculated by AppSheet, when you use the new Chart engine, grouped by week.
In this case, no matter how I wish to calculate the week number, I don’t have any control on it (at least I think ?)

That’s the starting point that made me having a deeper look into this.

Koichi_Tsuji

AppSheet is not 100% capable to accomodate all the different unique needs, but probably can meet with 99 perent, but we need to be more flexbile to think about how to fill our own unique needs, rather than waiting for new feature to come around.
Based on my past experience with Appsheet, the own problem could be solved without new features, but just we twist our own way of thinkings and have a own solution using exiting AppSheet functionalties.

When I face something I can not achieve, I firsly complain over myself, as my own deep thinking is prematured. More thinkings to get an answer without relying on new stuffs.

AppSheet is so deep to do something we don t know. The problem is we dont know what Appsheet actually can do.

Thinking thinking is one of fun parts to deal with Appsheet.
It is just a game and quiz programme.

Steve

shahappsheet

I am sorry that I am writing here. Please pardon me. I become interested to write here as I saw some of my honorable/knowledgeable guys( @Aurelien @Steve @Koichi_Tsuji ) are here together in this thread.

Anyway, would you please help me to calculate the working weeks between two dates like this, 21-12-2021 and 03-05-2022. It can be even more complex such as 21-12-2021 and 03-05-2023. Weeknum doesn't work in this scenario, if i am not mistaken. Should I do it by calculating the days and then divide by 7 to get the weeks? Or any other way. Please....🙏

Thanks a lot.

Aurelien

Hi @shahappsheet

I suggest you consider creating a new thread for your question, in the Q&A section 🙂

My personal suggestion would be to add a precision about your definition of a "working week", in this post.

Regards

Aurelien

Just adding an update here, and thank you @shahappsheet for getting me back to this old post: the function ISOWEEKNUM() is a perfect solution.

More reference here:

https://help.appsheet.com/en/articles/5496711-isoweeknum

shahappsheet

Thanks a lot @Aurelien . I will try and let you know.🙏