0

Week number vs ISO week number

Arttu Piipponen

May 19, 2023 06:52

None

Follow

I live in a country where ISO week number is the standard. Klipfolio "W" or "w" maybe uses more US based approach. Here is the explanation of difference from Micrsoft' site:

• WeekNum uses the week containing January 1 as the first week of the year. The result from this function can range from 1 to 54.
• ISOWeekNum uses the week containing the first Thursday of the year as the first week of the year. This follows the ISO 8601 date and time standard definition for week numbering. The result from this function can range from 1 to 53. It is possible that 52 or 53 may be returned for the first days of January since the dates could belong to the last week of the previous year.

So is there a function to use iso week numbers specifically? And yes, perhaps someone will answer that the location settings affects to this.
But is there function to use one the week number approaches in one klip and another approach in another klip? So actually a function?

• Facebook
• Twitter
• LinkedIn

8 comments

• 0
  
  Meggan King May 19, 2023 12:28

  Hello,

  To set the date at a company level, you would use locale in the Settings

  https://support.klipfolio.com/hc/en-us/articles/218143727-Locale-Setting

   

  To set the date at a Klip/formula level, you would use the function DATE_START_OF and select "week" and the day of the week for your week start

  https://support.klipfolio.com/hc/en-us/articles/360011416893-Using-functions-in-Klipfolio#DATE_STARTOF

  People commonly change the week or month of year start to align with their fiscal reporting vs their yearly reporting, but changing the week number to align to a different ISO is also common. 

  All our date formats are listed here:
  https://support.klipfolio.com/hc/en-us/articles/216182377-How-do-I-use-Date-Time-formats-

  Hope this helps,

  Meggan

• 0
  
  Arttu Piipponen May 19, 2023 13:13

  Thank you for the reply!

  So perhaps this would be the function:

  DATEVALUE(DATE_STARTOF(DATE("1.1.2022","d.M.yyyy"),week,0,Thursday),"yyyy/w")
  (the variables are the in plain text here)

  => 1.1.2022 and 2.1.2022 get the value 2021/52 which is correct

  BUT also the values 3.-5.1.2022 get the value 2021/52 which is incorrect, should be 2022/1 because those days are on the same week as the first Thursday of 2022.

  Probably adding something to each date first to hit the Thursday on that week and then performing the function above would give me the week numbers I am looking for? Or is there anything more elegant?

• 0
  
  Meggan King May 19, 2023 14:01

  Hello,

  When you use W or w for week, you need to use upper case Y for year, so try format "YYYY/w"

  

   

  The Klipfolio support team would need to look at your actual data to see if there is a more elegant way to do the function. Depending on where the DATE is coming from, you might not need to convert it with a formula and could use our Date formats to convert it to epoch/unix time. 

  Hope this helps

  Meggan

• 0
  
  Arttu Piipponen May 22, 2023 07:18

  DATEVALUE(DATE_STARTOF(DATE("3.1.2022","d.M.yyyy"),week,0,Thursday),"YYYY/w") => '2021/52' so no change compared to yyyy for year.

  DATEVALUE(DATE_STARTOF(DATE("3.1.2022","d.M.yyyy"),week,0,Thursday),"YYYY/W") gives '2021/5' and "YYYY/WW" gives '2021/05'.

  Or did you mean that year should be uppercase within the date function? I have learned to use uppercase for months because on minutes for lowercase but years, I thoughg, were irrelevant.

  I am currently playing with a klip with one label that has the funtion. There is not much to look at. I would apply the funtion for multiple klips once a solution is found.

• 0
  
  Arttu Piipponen May 22, 2023 07:21

  

  https://app.klipfolio.com/workspace/edit_klip/cb67bdda3742e128b2ebdcb762458239

• 0
  
  Arttu Piipponen May 22, 2023 07:39

  And it seems it can be made working though adding a DATE_ADD function

  DATE_ADD([date],"day",4-if(DATE_UNITVALUE([date],"day of week")=0,7,DATE_UNITVALUE([date],"day of week")))

  This should always give the Thursday of the week of [date] and then the solution you suggested earlier could be used. 
  I was hoping for a more elegant solution but Northern America based companies and coders seem to have certain limitations regarding international standards? :)

  

• 0
  
  Meggan King May 23, 2023 12:24

  Hello,

  The support team would need you to open a support ticket if you require specific help in your account. Your locale setting and your timezone will affect these functions

  

   

  You could share your settings here in the public community, or have the support team look at what you have configured in the Settings in your account.

  Thank you!

  Meggan

• 0
  
  Arttu Piipponen May 23, 2023 12:33

  I have once changed the location settings - and just how many klips needed rework after that :(

  So that is why I prefer writing these kind of settings directly into the klip / function etc etc. So if / when someone decides to change the locale settings, nothing is broken.