Grouping by Year, Month, and Week Number

I think that’s a bug, the last day of a year should be on the week 53 (or even 54 on leap years). A year has 52 full weeks and an extra day, so the last day always falls on the 53th week.

Interesting. I’ll note that for Airtable’s support team. Thanks for pointing that out.

If you want to group correctly like this, you need to use the ISO Week Year instead of your Publish Date year directly. Change your “Year” column to DATETIME_FORMAT({Publish Date},‘GGGG’). Since 12/30/2019 falls in the ISO week year 2020 and you are using ISO week numbers, you need the year column to return 2020 for 12/30/2019.

This isn’t a bug, it’s just unexpected behavior of date math, which is incredibly unintuitive. You can’t combine ISO week numbers with regular years, you have to match them with ISO week years.

Hey, thanks for your response. This almost got me where I’d like the table to be, but there are a few hiccups. After changing the “Year” column to Week year (ISO) and “Week #” column to Week of the year (ISO), 12/29/2019 was added to the appropriate spot in December.

But now 12/30/2019 and 12/31/2019 have moved to their own grouping in 2020, and 12/31/2018 is its own group in December of 2019. Any thoughts on how to fix this? Thanks.

Hmm, ok, well this is being caused by the same class of problem we solved with the the year formula, except that now the problem is with the month part of the grouping—you’re currently combining the gregorian month with the ISO year & week. The simplest way to fix is to group using some different measure, e.g. using quarters instead (Q1=weeks 1-13, etc.) but if you truly need to group by month, you’ll have to figure out how to map ISO weeks back to some type of ISO month.

I can’t quickly figure out how to determine the first day of an ISO year in airtable… any formula experts have ideas on how to do that? Paging @W_Vann_Hall… If you were able to somehow compute the first day of the ISO year [DAY1], then you might be able to do something like MONTH(DATEADD(DAY1, {ISO week number}, ‘weeks’))

See also https://en.wikipedia.org/wiki/ISO_week_date#Weeks_per_month for a little background on this mapping problem.

Hey, thanks so much for your input, @drassi! I was looking up ISO information on months and saw that same thing; where months are not standardized or defined.

If I could compute every day in a year (DDD) as a number (For example, January 1 = 1, December 31 = 365), and then group by days inside of each month, that could potentially solve my issue as well, I think.

For example, inside of January, days are grouped by 1-7, 8-14, 15-21, 22-28, and 29-31. Is that possible without getting into any crazy complex ISO formulas?

Really my end goal here is to just have months broken up into easily distinguishable chunks of days in a grouped view. Thanks again for your thoughts.

Ah, hey think I found an iso month solution, if you want to go that path. It’s based on the fact that Jan 4 is always in ISO Week 1, which I realized after reading the wiki entry again.

MONTH(DATEADD(DATETIME_PARSE({ISOYear}&"-01-04"),{ISOWeek}-1,‘week’))

That will sort 12/30/2019 & 12/31/2019 into ISO week 1 grouping of January 2020.

Also yes, of course you can split up using Gregorian months based on day of the month instead of ISO week, but I figured your workflow made more sense splitting up each Monday-Sunday week.

Thanks! Appreciate you thinking through this. However, any chance you’d know how to work through the error I’m receiving now?

For reference, I’ve changed the names of my Year and Week fields to correspond to what you have. Here are their formulas currently:

ISOYear
DATETIME_FORMAT({Publish Date}, 'GGGG')

Month
MONTH( {Publish Date} ) & " " & DATETIME_FORMAT( DATETIME_PARSE( MONTH( {Publish Date} )&'', 'M' ), 'MMMM' )

ISOWeek
DATETIME_FORMAT({Publish Date},'W')

(I don’t think I’ll actually need the Month field as it is currently, but I included it here just in case.) Appreciate any help you can give.

I think the forum software might have converted the straight single quotes 'week' to angled single quotes ‘week’, I didn’t type them in a code block. See if fixing that back to straight single quotes helps

It will also change double quotes – " " – to “smart quotes” that angle based on their location as well. So he’d do well to also manually delete and retype all the double quotes in the formula.

Thanks so much @drassi and @Jeremy_Oglesby! This is exactly what I needed. Everything is grouped and displaying correctly now.

One last question, do you know of a way to display the ISOMonth name as # MonthName or just MonthName? For example, “1 January,” “6 June,” or “October,” “December,” etc.

Currently ISOMonth displays as just a number, which works, but for the sake of being a bit more human-readable, do you think this is possible?

I tried following some suggestions from this existing thread, (Group entries by month), but the groupings by month get jumbled up, because I believe Airtable wants to group them alphabetically. For example, 11 November displays above 10 September since they’re alphabetized. Any thoughts on that?

Again, appreciate all the work you’ve put into this.

Just use the DATETIME_FORMAT function as you’ve already done. Use a zero-padded month first so things will sort correctly, then the month name as you want to display it

So something like change your MONTH(xxx) function to DATETIME_FORMAT(xxx, ‘MM MMMM’)

In my experience, since DATETIME_FORMAT() is outputting a proper DATETIME data type, even if formatted as a string, it still sorts properly without needing to zero-pad the month numbers. It seems the sorting is taking place with reference to the raw DATETIME data behind the formatted string it displays.

Here’s proof:

Ah, you’re right @Jeremy_Oglesby, I tried without zero-padding and it worked.

And I think that solves my issue, thank you very much @Jeremy_Oglesby and @drassi, you’ve been extremely helpful.

@drassi, @W_Vann_Hall Do you have a solution to the this leap year problem? I am getting Week 1 for 12/30/2019 and Week 2 for 1/6/2020

I guess my question is, “What would you like to see?”

As far as I know, only every seventh (?) leap year contains 53 weeks of 4 or more days — 2004 was the most recent.

There are ways to calculate whatever you want – if you tell me what it is you want. (I’ll use the US standard week of Sunday - Saturday in my examples; let me know if that’s not appropriate.)

Based on a given {Date}, do you want the week in which it falls based on

1. the number of 7-day weeks in the year (for instance, January 1 - January 7 would always fall in Week 1, regardless of {Dates}'s day of the week)?
2. the number of calendar weeks which contain any days of the given year (for instance, if January 1 falls on a Saturday, then Sunday, January 2, would belong to Week 2)?
3. the number of calendar weeks containing 4 or more days of the given year? (This should be what DATETIME_FORMAT() provides for a format specifier of ‘w’/‘wo’/‘ww’ or ‘W’/‘Wo’/'WW/, depending on whether or not you want ISO weeks.)

or

4. something else?

Let me know, and I’ll see about putting together a formula…

just in case someone stumbles onto this topic like me, before you waste your time…

there is now a formula for weeknum that returns the week number as a numeric value.

.

That has been a function for a while – and, if you reread the OP’s comment, it doesn’t necessarily return the expected result. For instance, WEEKNUM('12/28/2020') – in MM/DD/YYYY format – returns 1, as that date falls in a week that also contains 01/01/2021. In other words, WEEKNUM() returns the number of the week of the year of the last date (Saturday or Sunday, depending) of that week.

Also if you have data from multiple years. use CONCATENATE(YEAR(Date),"-WW",WEEKNUM(Date))

: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

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