Grouping by Year, Month, and Week Number

[Bryce_Schmidt]

Hi, I’m creating a running list of records (in this case, social media posts) and I’ve organized them by year, then by month, and then by week. To do this, I assigned format specifiers to each date with formulas. For example, 2019 is “2019”, January is “1 January,” February is “2 February,” etc. Weeks are assigned the same way – the first week of January is “1,” the last week of December is “52.”

However, I ran into trouble with a new grouped view (“Posts by Month”). I grouped these records by year, month, and week. Everything displays as it should except the last few days of December. 12/29/2019 through 12/31/2019 disappear. They are technically part of week 1 in 2020, but aren’t displaying because they are still part of the year 2019.

Is there way to force Airtable to recognize these last few days in December are part of 2019 and to show up in this grouped view? Perhaps as Week 53 in 2019?

[Christopher_Ber]

:roll_eyes:
Hi, I have a question regarding the problem “calendar” week, from an european/german perspective:

• When I use the formula DATETIME_FORMAT(Date,'W') I get for the week starting on Monday, January 3 in 2022 as a result “52”
• When I use the formula WEEKNUM(Date) I get for the week from Monday, January 3 in 2022 to Sunday January 9 2022 as a result “2”
• I understand that the Weeknum Formula is working as “American” → the first week with January 1 is the week “1” of a year. In Europe it is the first week with 4 days of January in it… starting on a Monday, not Sunday!

So basically I need a formula (?) that has the calendar-week-output “01” for the week from Monday, January 3 in 2022 to Sunday January 9 2022… :slightly_smiling_face:

Does anybody has an idea? it would be greatly appreciated!
Christopher

[Artemix_Artemix]

DATETIME_FORMAT parameters support different Region’s weeknumber : DATETIME_FORMAT - Supported Formats | Airtable Support