In Microsoft Excel the following formula forces the month name to be displayed in Portuguese, regardless of the default language.

=TEXT(A1,"[$-pt-PT] dd mmmm yyyy")

Google Spreadsheets ignores the parameter [$-pt-PT]. How can I format dates to different language than the default on in Google Spreadsheets?

  1. Dates are displayed based not on the browser default language, but on the spreadsheet locale, which is set in File > Spreadsheet options.
  2. You can also use the =GOOGLETRANSLATE() formula.
  • Well the purpose of the question is to have multiple languages, right? So 1st point is out of the window. The 2nd is an okay idea, but it's a just such a shame google sheets ignores that parameter – Calvin Jul 13 '17 at 14:37
  • Was that indeed the purpose of the question? Just reading it is a little unclear what default you had in mind when you wrote "different language than the default on in Google Spreadsheets". I would say it's quite an improbable requirement to have multiple language date formats in the same spreadsheet. – ttarchala Jul 13 '17 at 15:57

Since text does not support language parameter, this seems to require manual "translation". I would put the Portuguese month names somewhere in the spreadsheet (they could be on another, hidden sheet) and use the following:

=text(A5,"dd ") & index(S1:S12, month(A5)) & text(A5, " yyyy")

Here S1:S12 is the range in which the month names are contained. The index picks the correct month based on the date, which is concatenated to the rest of the date.

  • That is how I am doing it at the moment, but I really wanted to do it better. – Calvin Jul 13 '17 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.