google-sheetsformuladate-formatting

Cannot extract month name, in specified language, from date


I cannot extract the month date from a cell.

The cell contains a valid date (when you click on the cell a calendar view opens).

The format of my cell is: DD/MM/YYYY

In another cell I want to extract the month name in English from that date cell.

I do: =MONTH(A1) And I get the number of the month. Great so far. However, I want to get the name of that month extracted (e.g. July)

So I do this: =MONTH(A1; "MMMM")

However I get an error as a result and it says this: Wrong number of arguments to MONTH. Expected 1 arguments, but got 2 arguments.


Solution

  • Regarding your error, you are using MONTH instead of TEXT. Instead, do: =TEXT(A1, "MMMM").

    Or since it seems the settings on your computer are to use ; instead of ,: =TEXT(A1; "MMMM").


    Now, regarding the language issue mentioned in comments (I took the liberty of updating your question), the "recommended" solution you found (and also documented here) is to use GOOGLETRANSLATE.

    Since you are currently getting the month in German (ISO code de) and you want it in English (ISO code en), the formula would be =GOOGLETRANSLATE(TEXT(A1;"MMMM");"de";"en").


    PS: For Excel users out there, the TEXT function supports specifying the locale directly in the format. Simply replace "MMMM" by "[$-en-US]MMMM" to get =TEXT(A1, "[$-en-US]MMMM").

    An alternative to using a formula in a separate cell to get the date in the form of text, you can use the above format string as the number format parameter of your date cell (Right click on the cell > Format cell > Go to tab [Number format] > Select [Custom format]).

    In that same window, you will find the list of locales you can use for the formatting (under [Date] instead of [Custom format]).