Group date by month


#1

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.


#2

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!


#3

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?


#4

You can create 2 formula fields; one for year and one for month. Group by the year field first, then by the month field.


#5

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.

#6

How were you able to get your months as #s instead of the words? Mine is sorting alphabetically instead of chronologically.


#7

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)


#8

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!