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.
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.
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!
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!
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
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
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.
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.
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
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
@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.
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
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"))))))))))))
@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.
@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 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?
@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.
@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.
Jeremy – that is sweet! Wish I had thought of it myself, but so glad you did!
@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.
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! ¯_(ツ)_/¯
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! ¯_(ツ)_/¯
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 
@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.
@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 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!
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.
@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!
If you need it to be a date, you can wrap it in DATETIME_PARSE().
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.
No worries, Jeremy. I appreciated your elegant solution above nonetheless, and using @W_Vann_Hall’s suggestion below, achieved the right outcome. Cheers!
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
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
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!
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
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.
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.
I also need the same thing
