CODEWITHSUNDEEP
excelexcel-formula
Hakan ERDOGAN
Hakan ERDOGAN

Reputation: 1210

Formatting dates in different locales

I have a date value in cell A1 and in A2 I am converting this to show only its month name with TEXT formula. My locale is Turkish but need to show the month in English. Come up with below formula which works fine on my computer.

But when this Excel file is opened at a computer with English locale, the user sees "aaaa" in the cell because in English locale, month formatting string is "mmmm" but not "aaaa". When I change the string to "mmmm", then I will not able to see the month but "mmmm" string instead.

Is there any pure-formula method to overcome this problem?

TEXT(A1,"[$-en-EN]aaaa")

Upvotes: 2

Views: 274

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

There may be a better method, but this one worked for me:

=TEXT(A1,IF(TEXT("1/1/2018","[$-en-EN]aaaa")="January","[$-en-EN]aaaa","[$-en-EN]mmmm"))

Upvotes: 2

Related Questions