Skip to main content

New year problem with Week #

  • January 9, 2020
  • 4 replies
  • 50 views
E
Forum|alt.badge.img+2

So i have a simple formula for calculating the week number to make grouping the records easier. When 2020 came around it got grouped with 2019 so i switched it to a DATETIME_FORMAT to show both year and week. ezpz should be solved.
But noticed that for some reason the 27th-31th of December in 2020 is calculated as weeknum 1 of 2020? Also tried setting the timezone but no luck there. The days of the week are also shifted. At least in Sweden (and i would guess most places?) Week 1 of 2020 starts 30th Dec and ends 5th Jan. Any takes on why Airtable calculates it like this?

      This topic has been closed for replies.

      4 replies

      C
      Forum|alt.badge.img+9

      This formula should solve your problem

      IF(AND(DATETIME_FORMAT(date,“YYYY - Wo”),DATETIME_FORMAT(date,‘M’)=12),CONCATENATE(DATETIME_FORMAT(date,“YYYY”),"- 52"),DATETIME_FORMAT(date,“YYYY - W”))

      E
      Forum|alt.badge.img+2
      • Emil_Lilja
      • Author
      • Participating Frequently
      • January 10, 2020

      This formula should solve your problem

      IF(AND(DATETIME_FORMAT(date,“YYYY - Wo”),DATETIME_FORMAT(date,‘M’)=12),CONCATENATE(DATETIME_FORMAT(date,“YYYY”),"- 52"),DATETIME_FORMAT(date,“YYYY - W”))


      Smooth solution man!

      Although for some reason this created another problem that i also can’t figure out, all of Dec 2019 gets grouped to week 52?
      Better than before but still

      C
      Forum|alt.badge.img+9

      Smooth solution man!

      Although for some reason this created another problem that i also can’t figure out, all of Dec 2019 gets grouped to week 52?
      Better than before but still


      IF(AND(DATETIME_FORMAT(date,“W”)=1,DATETIME_FORMAT(date,‘M’)=12),CONCATENATE(DATETIME_FORMAT(date,“YYYY”),"- 52"),DATETIME_FORMAT(date,“YYYY - W”))

      I messed up on the first part of the formula. This should fix it for you.

        E
        Forum|alt.badge.img+2
        • Emil_Lilja
        • Author
        • Participating Frequently
        • January 13, 2020

        IF(AND(DATETIME_FORMAT(date,“W”)=1,DATETIME_FORMAT(date,‘M’)=12),CONCATENATE(DATETIME_FORMAT(date,“YYYY”),"- 52"),DATETIME_FORMAT(date,“YYYY - W”))

        I messed up on the first part of the formula. This should fix it for you.


        My man!

        Creative solution

        Terms & ConditionsAccessibility statement