Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

Group date by month

cancel
Showing results for 
Search instead for 
Did you mean: 
Jan_Drabek
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

11 Comments
Katherine_Duh
10 - Mercury
10 - Mercury

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!

Jan_Drabek
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

Katherine_Duh
10 - Mercury
10 - Mercury

You can create 2 formula fields; one for year and one for month. Group by the year field first, then by the month field.
Screen Shot 2017-07-13 at 11.24.40 AM.png

adam
5 - Automation Enthusiast
5 - Automation Enthusiast

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.
Lillian_Shaw
4 - Data Explorer
4 - Data Explorer

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

Lillian_Shaw
4 - Data Explorer
4 - Data Explorer

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)

Jack_Franz1
4 - Data Explorer
4 - Data Explorer

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!

Alex_Githatu
4 - Data Explorer
4 - Data Explorer

This can also be shortened to:

DATETIME_FORMAT(Date,'MM - MMMM')

Mmvcko
4 - Data Explorer
4 - Data Explorer

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)

April_Kastner
6 - Interface Innovator
6 - Interface Innovator

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