Hello,
When checking the workbook for Excel Dates Displayed in Different Languages I noticed that the custom number format for months did not show correct, so I checked it out. It seems to be that I who use Swedish as Regional language have to use capital M instead of small m when defining the custom number format.
Now I wonder why. I have a O365 Home subscription and Excel 2016 MSO (16.0.8326.2076) 32-bit.
When checking one reference article at Microsoft it seems ok to use small letters. But as you can see in the attached picture, I only get 00 as result instead of the months name and its abbreviation. If I change the formatting to capital M then I get correct result. The formula for the January result is now the following: =TEXT($L$4;VLOOKUP($I$4;Table1[[Language]:[Language ID]];3;0)&"MMMM")
If someone knows more about this "issue" then I would be more than happy to get more information about this.
Br,
Anders
Hi Anders,
Is the Excel version you are using in Swedish or in English?
I know that text function behaves differently if the installed excel version is in a different language, for example, for german version of excel, to format as dd/mm/yyyy you have to use:
=Text(A1,"tt/mm/jjjj"), for french: =Text(A1,"jj/mm/aaaa")
Can you switch from Swedish to English language pack to test if the problem remains?
Hi Catalin,
Yes, it is a Swedish version. I am using the English language pack though. I will do some test later on with switching Windows language and also try with other regional setting. Can be a good thing to also test using the language tag instead of the id.
Thanks for your support, it is appreciated.
Br,
Anders
Hi,
The problem seem to be with Windows 10 and display language set to Swedish (and possibly other languages as well). As the pictures shows it works fine when I change Windows display language to English, but if I stick with my native language the formatting for month using small m makes Excel to treat that formatting as minutes instead of month. I also noticed that the formatting where I use capital M works fine with English display language.
Seems that one needs to check this "issue" if you are about to share any date formatted Excel file internationally.
I also noticed another issue, but this time related to the language id. Seems to me it would be better to use the language tag as LCID as some language id's are the same for different languages, for example this one shown in the picture, id 0x1000, or as it is written in Excel, [$-1000]. When I have Swedish as Windows display language I get Swedish result for the name of the day for the Cornish language, just because same language id is used for Åland islands.
So now I know at least why this "issue" occurs. Never seen this problem before though and I have used Excel since 1996.
Br,
Anders
Thanks for feedback Anders,
I've seen the TEXT function used in visual basic UDF, to overcome regional settings (as there can be many variations, as described in previous message for german and french languages), in VB all the formats are in english only.
Cheers,
Catalin
