Group date by month

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?

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

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.

How were you able to get your months as #s instead of the words? Mine is sorting alphabetically instead of 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!

This can also be shortened to:

DATETIME_FORMAT(Date,'MM - MMMM')

This is great, thanks all.

What would be the formula for grouping by day-month-year and having that show up as the following on Airtable?

e.g 01-January-2019 (when grouped)

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

Is there still no way to group by chronological order from this formula field? I’m able to sort it all with these formulas, but getting an easy to read name (i.e. November 2022) to group by is proving to be difficult since it only wants to allow it to be alphabetically done. Thanks!