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?

[Jeremy_Oglesby]

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.

[Bryce_Schmidt]

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.

[drassi]

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’)

Airtable - Support

Supported format specifiers for DATETIME_FORMAT

The DATETIME_FORMAT function will allow you to reformat the data from the date-type field into a string of your specifications. This is written in the form "DATETIME_FORMAT(Datetime, 'format s...

[Jeremy_Oglesby]

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:

[Bryce_Schmidt]

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.

[Manager_DGP]

drassi:

DATEADD

@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

[W_Vann_Hall]

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…

[darren_mark]

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.

.

[W_Vann_Hall]

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.

[Rambo_Goel]

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