I’m using Airtable now for almost everything and I love grouping feature. But when I group by date it groups by every single day but possibility to group by month would be better because there would be only 12 groups instead of 1 to 365.
If you create a separate formula field using DATETIME_FORMAT, and group on that new field, you can group by month (or week, or business quarter, etc.). Here’s our support article for how to do it!
Ok but when I do It I’m not able to sort dates chronologicaly because it only goes 1 - 9 so i can have months like this 1/2017, 1/2018, 3/2017, 4/2017, 6/2018 etc. is that right?
This works great, and thank you for the Grouping on a date range article, @Katherine_Duh!
I think a nice extension to this is to have a way to still sort in chronological order while being able to use the month common name (“MMMM”) instead of the month number.
If you pull out the month name using DATETIME_FORMAT({Date}, "MMMM"), your output value is text and sorts/groups alphabetically.
If you pull the month using MONTH({Date}) it would be great if there was an optional parameter to specify either the number or name. If the name was of a date data type and not text it would not sort alphabetically, but chronologically.
Figured it out! Also, I used the concatenate featured to combine the Month number and written out, so I can read the month but have it ordered chronologically. I just hide the extra non-combined fields. Airtable win!
You have to create the Month# field and Month Name field, then use the formula: CONCATENATE({Month#} & “-” & Month Name)
Using DATETIME_FORMAT and concatenation is all well and good, but Airtable should still have a Group option by Month, Quarter, Year, etc., when the record only contains a Date. And it should sort chronologically. Having to do the above for every table where I want to group on month is a real drag!
I know this is old, but if I use this formula, do you know if there is a way to have it only display the ‘MMM’ in the field rather than both ‘MM’ & ‘MMM’?
