Group entries by month

[Geoffrey_Badner]

I run a photo studio that photographers rent out for the hour/day and I use Airtable to keep track of these bookings. I like to view bookings grouped by month so I can get a sense of how sales are doing each month.

To do this currently, I have bookings sorted by “start date” which is a date field (eg. “01/02/18”). This places all bookings in newest to oldest order (9-1), but everything runs together and and I can get a sum total of monthly sales easily. To address this, I added text a field called “month” to each entry and manually type in the month (eg. “01 January”) when I log a booking. After that, I created a “group by” rule that groups everything by the month column.

This worked ok for 2017, but now it’s 2018 and 2017’s January bookings are mixed with 2018’s. The obvious solution would be to add a text field with “year” (eg. “2017”, “2018”) and then group by month AND year. However, it seems dumb to me that I have to manually add month and year to my record when I’m already using a date field. It’s 2 more fields to manage that seem redundant to the Start Date field.

Any advice on how to do this? Thanks!

[AlexWolfe]

If you use the MONTH() and YEAR() formulas (new fields), they will automatically pull the month and year text so you do not have to manually enter them.

Note that if you group by a formula field you cannot add or move records. But if you manage the date and other inputs in a different view this won’t be an issue.

[Geoffrey_Badner]

Thanks for the response, but I’m not following what you’re saying. I’m not
great with formulas. Can you be more specific? Thanks!

[John_Beaudoin]

Geoff,

I actually use this for the same reason. If you have a date field – let’s call it TheDate – you can create a second field – let’s call it TheMonth – and display the results of the formula Month(TheDate) in it, rendering only the month number of the date. For instance, January 2, 2018 would yield a result of “1”

You can create a third field – let’s call it TheYear – and display the results of the formula Year(TheDate) in it, rendering only the four-digit year element of the date. For instance, January 2, 2018 would yield a result of “2018”

In your view, you can group by TheYear and then by TheMonth to separate your items – and still get your summary data (I think).

Post back if this doesn’t work for you.

JB

[Jordan_Dayton]

Hey @John_Beaudoin what would would I do if I’m using MONTH({full_date_field}) and am getting the numbers of the month as a result (as you suggested) but I want it to actually be the name of the month… i.e. Jan, Feb, or the full month name; i.e. January, February… instead of 1, 2, etc.

[John_Beaudoin]

Well, I would create a field called TheMonthName and use a formula that looks like this:

IF(MONTH(TheDate)=1,"January",IF(MONTH(TheDate)=2,"February",IF(MONTH(TheDate)=3,"March",IF(MONTH(TheDate)=4,"April",IF(MONTH(TheDate)=5,"May",IF(MONTH(TheDate)=6,"June",IF(MONTH(TheDate)=7,"July",IF(MONTH(TheDate)=8,"August",IF(MONTH(TheDate)=9,"Septemeber",IF(MONTH(TheDate)=10,"October",IF(MONTH(TheDate)=11,"November","December")))))))))))

One caveat: there’s nothing “special” about the result of the formula – to Airtable, these are just text values. That has implications for grouping, filtering and sorting in views. When you number a month from 1-12, you have more options in displaying the results. For instance, you can sort the months 1->9 when the result is numeric, but if your months are grouped by name, the alphabetized sorting leads to April ahead of February, for instance. So you might consider a combination of numeral-text, such as 01-January, 02-February, etc.

JB

[Jeremy_Oglesby]

@John_Beaudoin @Jordan_Dayton , there’s another way to do this that uses another field, but allows a much simpler formula.

Make a field TheMonthNumber with formula:
Month( TheDate ).

Make a second field TheMonthName with formula:
DATETIME_FORMAT( DATETIME_PARSE( TheMonthNumber&'','M' ), 'MMMM' )

This will return the month as a name, and has the bonus that it’s still recognized as a Date type of data by Airtable, rather than just a string - so other Date type formulas can still reference it. You can hide the TheMonthNumber field to reduce clutter since it’s a formula and only passes data on to another formula.

[Jordan_Dayton]

Brilliant! I had to tweak your formula per your suggestion, adding the number as a prefix to the month name. Also had to finish the formula and add an extra close paren for Dec. But it worked masterfully!

IF(MONTH({Date Published})=1,"01-January",IF(MONTH({Date Published})=2,"02-February",IF(MONTH({Date Published})=3,"03-March",IF(MONTH({Date Published})=4,"04-April",IF(MONTH({Date Published})=5,"05-May",IF(MONTH({Date Published})=6,"06-June",IF(MONTH({Date Published})=7,"07-July",IF(MONTH({Date Published})=8,"08-August",IF(MONTH({Date Published})=9,"09-Septemeber",IF(MONTH({Date Published})=10,"10-October",IF(MONTH({Date Published})=11,"11-November",IF(MONTH({Date Published})=12,"12-December"))))))))))))

[Jordan_Dayton]

@Jeremy_Oglesby I’m about to try this, too! Thanks for chiming in!

I guess I should post my real question and see if you can help…

I want to be able to group my table first by the year, then group within that by month, in order of the months. Would there be way to do this, using the formula field, instead of having to create two additional fields (Year, and Month) which i’m doing right now?

[Jeremy_Oglesby]

@Jordan_Dayton

I’m doing basically the same thing as you in my table.

I had to prefix my month names with MonthNumber as well to get them to order properly, so I am extracting the Year:

as well as the Month Number:

to generate the Next Due Month:

Which allows me to group on Year and Month, with Months showing in the right order:

And I found that I did not need to use the leading “0” for single digit months (I expected to have to do that, as you did) - Airtable still puts “3 March” before “10 October”!

You could get even more granular by also extracting the DAY( {Date} ), and adding a third grouping within the Month, based on the DayNumber, or just simply Sorting within the Month by DayNumber.