Extract month from date field

[Vera Milosavich]

Is there a formula to extract the month (preferably month name) from a date field? I haven’t been able to find anything.

[Brendan]

Hi Vera,

Sure! Use the DATE() function in a Calculation field formula to do this.

For example:

DATE(Birthdate; "MMM")

That will give you the month name from the provided Birthdate field.

There are all sorts of date patterns you can use to get different date formats in a formula.

Please see this page for a list of the date patterns available to you.

http://www.unicode.org/reports/tr35/tr35-31/tr35-dates.html#Date_Format_Patterns

Thanks!

Brendan

[Brendan]

Oh, one other thing, on the Formula Edit screen, make sure you set the Result Type to Text.

[Vera Milosavich]

Thank you Brendan — just what I needed!

[Vera Milosavich]

I have a date-formatted field named SaleDate and a calculation-formated field named _month-year containing this formula: CONCAT(DATE(SaleDate;”MMM”);”-“;DATE(SaleDate;”yyyy”)) and the Result Type is set to Text.

The result I want is: April-2020

The _month-year field displays nothing. I also tried using simply DATE(SaleDate;”MMM”) as the formula and it doesn’t display anything either.

Am I doing something wrong?

[Sam Moffatt]

Make sure you’re not using smart quotes but are using normal quotes. Looking at what you posted, those look like smart quotes. MacOS is usually good about giving you a plain double quote when you ask for it but iOS likes to help you with that when you’re entering text using it’s keyboard. In the calculation editor there is a double quote button in the blue bar above the keyboard that will insert a plain double quote for you or if you tap and hold the quote button on the iOS keyboard you can insert a normal quote.

[Sam Moffatt]

As an aside I’ve personally lost a stupid amount of time due to smart quotes which is why I know to look for them. I rebuilt an entire calculation from scratch multiple times before I realised iOS was giving me smart quotes and messing with me.

[Vera Milosavich]

Thank you for pointing that out because it hadn’t even occurred to me here. I was so hoping it was a simple as that because I’m forever doing this as well. But I double checked and they are indeed plain quotes in the actual calculation. It must’ve converted them when I pasted in the formula. I do not have the iOS version (yet)…

I’m still testing the desktop version during the free trial as a possible replacement for FileMaker (decades-long user) which has gotten far too expensive for my now limited needs. If I can at least FIND the road to rewriting the one critical dB I still need, I will drop FileMaker. But it worries me that I’ve been stuck on such a seemingly simple formula — and I haven’t even gotten to serious relational files or layout design! I’m desperately hoping it’s just a paradigm shift that I haven’t grasped yet.

[Brendan]

Hi Vera,

When you put the fields into your formula, do they show as blue tokens? Or did you just type in the field names? If so, then that may be the problem. You need to double-click on a field to insert it into your formula.

You can also concatenate just by adding things together instead of using the CONCAT function.

So try:

DATE(SaleDate;"MMM") + "-" + DATE(SaleDate;"yyyy")

[Brendan]

Also, to get what you want, you could just do it in one call:

DATE(SaleDate; "MMM-yyyy")

[Sam Moffatt]

Could you consider supporting the square bracket field name format on the desktop as well? This would make it consistent with iOS and a little easier to type in field names. Copying the blue field placeholders is also impossible when putting onto the forum :(

[Vera Milosavich]

It was the token issue. The calculation works now — with DATE(SaleDate; "MMM-yyyy"). I only ended up with CONCAT() before posting after everything else failed, not even realizing my problem was with tokens and not the formula!

Double-clicking to get the token in wasn’t working the way I expected which is why I resorted to typing the field in… I selected what I wanted to replace with the token, then double-clicked the token. Instead of replacing, it selected the entire formula and then overwrote it with the field. I played with it more after your reply and now see that I have to delete the placeholder text and just leave the cursor (that is, not have any text selected) before double-clicking the field. I haven’t seen it done this way before (and didn’t find it explained in the manual), but at least now I know. Thank you!

[Author]

Posts