New year problem with Week #

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?

2020-01-09 12_44_27-Shipment Tracker_ Yggdrasil - Airtable 2020-01-09 12_45_01-Shipment Tracker_ Yggdrasil - Airtable

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

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

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.