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!

[John_Beaudoin]

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

[Jordan_Dayton]

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! ¯_(ツ)_/¯

[Jeremy_Oglesby]

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:

[John_Beaudoin]

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

[Jeremy_Oglesby]

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:

Jeremy_Oglesby:

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

[W_Vann_Hall]

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

[John_Beaudoin]

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

[Meghan_Lundy]

Can someone please post the final function. I read through the whole string, but am not understanding what the final outcome was.

I just want to sort by month as a date and not a text field, and not have to type the month in every time.

Meghan

[Games_Atlas]

Thanks for the great insight, helped me as well!

I worked out that a final Formula can actually look like something as simple as this:

DATETIME_PARSE( MONTH(Date Due),'M' )

And in Formatting option give it a Date format of “Friendly”.

This will result in something like “February 1, 2021” for all things that are in the month February. Only downside is that there will be an extra “1” visible in each month.

But this allows to sort and group by month and have Airtable still treat it as a date. It’s the only way to do so, as unfortunately, we can’t select just “Month” in the Date formatting options (which can be done in Excel or Google Sheets but not in Airtable).

But if you ignore the extra “1”, it works!

[Pavel_Kovalyov]

Hi!

YEAR({Date}) & " - " & MONTH({Date}) & " - " & DATETIME_FORMAT(DATETIME_PARSE(MONTH({Date})&'','M'),'MMMM')

I am using this to have such output: “2022 - 11 - November”. With this output, I can easily group by year and month, and also have month names to get easier visual recognition. With such system, my records are not mixing between years.