Search code examples
if-statementgoogle-sheetsformulalocalearray-formulas

Arrayformula not working for Google Spreadsheet settings having locale other than US

I have created a Google spreadsheet with Locale set to Brazil and Timezone set to (GMT-03:00) Sau Paulo that has a Date column having dates in their regional format like 01-jan.-2021, 23-fev.-2021 etc.

I have created a Month column next to this Date column and trying to populate the months using Arrayformula:

=ARRAYFORMULA(IF(LEN(F2:F),MONTH(F2:F),))

It doesn't seem to work if i use IF or LEN in the Arrayformula function, however, seems to work if i simply do this:

=ARRAYFORMULA(MONTH(F2:F))

which obviously fills down all rows including blank date cells and adds more rows.

Another thing i noted is, if i change the Spreadsheet settings to Locale : United States & Timezone : Eastern Time (GMT-04:00), the Arrayformula with both the IF & LEN functions seems to work! However, it doesn't work for the above region.

If anyone has an alternative solution to make it work, please do share here!

Solution

  • In a Google Spreadsheet, the arguments in any given function are separated either by a decimal point OR a decimal comma. The applicable separator depends on the spreadsheet locale (File, Spreadsheet settings, Locale) and the decimal separator used by that country

    • If the locale is a country that uses a decimal point (.) as a decimal separator, then function arguments are typically separated by use a comma (,).

    • If the locale is a country that uses a decimal comma (,) as a decimal separator, then function arguments are typically separated by use a semi-colon (;).

    Brazil uses a decimal comma, so Google Sheet arguments are separated by a semi-colon.

    • =ARRAYFORMULA(IF(LEN(F2:F),MONTH(F2:F),)): FAIL
    • =ARRAYFORMULA(IF(LEN(F2:F);MONTH(F2:F);)): SUCCESS

    United States uses a decimal point), so Google Sheets should be separated by a comma.

    • =ARRAYFORMULA(IF(LEN(F2:F),MONTH(F2:F),)): SUCCESS
    • =ARRAYFORMULA(IF(LEN(F2:F),MONTH(F2:F),)): FAIL

    The snapshots below are taken from two spreadsheets (#1 locale=Brazil, #2 Locale=USA) and demonstrate the application of a comma or a semi-colon as a argument separator.

    Brazil

    Brazil

    USA

    USA