Group entries by month


#1

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!


#2

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.


#3

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!


#4

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


#5

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.


#6

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


#7

@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.


#8

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"))))))))))))

#9

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


#10

@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.


Roll up several Fields (columns)
#11

Jeremy – that is sweet! Wish I had thought of it myself, but so glad you did!


#12

This is brilliant!

So here’s my next challenge for you… or @John_Beaudoin or anyone else…

I’d like to just have my date column and ONE other (formula) column that will do all of what we’re talking about, without having to create 2, or 3 additional formula columns. I know I can just hide them but I want it to be as clean and simplified as possible! ¯_(ツ)_/¯


#13

Well, you can definitely condense my MonthNum and Next Due Month fields into one like so:

MONTH( {Next Due} ) & " " &
DATETIME_FORMAT(
                DATETIME_PARSE(
                               MONTH( {Next Due} )&'',
                               'M'
                               ),
                'MMMM'
                )

I think you will have to have that column and the {Next Due Year} column as separate columns though if you want to group on both. You can’t create two groupings (Year and then Month) based on a single column.

So this brings you down from 4 columns to 3 - I don’t know if you can go any lower though :disappointed_relieved:


#14

@Jeremy_Oglesby, I just tried your approach and TheMonthName is being treated as text (I think). If I sort, I get an alpha sort (A-Z or Z-A) and if I try to format the field Airtable says “Your result type is not a number or a date…” Any ideas what I could have done wrong? I cut and pasted the formula you had in message 6… The input was a number generated by Month( TheDate ). Thanks for any suggestions!


#15

I’m sorry, @John_Beaudoin - I just realized what you were saying - my original post was way off the mark.

You’re saying that this formula:

DATETIME_FORMAT(DATETIME_PARSE(MONTH({Next Due})&'','M'),'MMMM')

is returning a string, not a Date type of data. I said earlier that it would return a date:

You appear to be correct that I misspoke there - it does indeed return a string, which is no longer recognized by any “Date” functions. Sorry about that, @John_Beaudoin. My mistake.


#16

If you need it to be a date, you can wrap it in DATETIME_PARSE().


#17

No worries, Jeremy. I appreciated your elegant solution above nonetheless, and using @W_Vann_Hall’s suggestion below, achieved the right outcome. Cheers!