Excel TEXT() Formula Getting the Month returning "mmm"

Question

I got a report in excel and I'm having some trouble with a Russian PC. The formula TEXT is being used to get the month name from a date but, in the Russian PC it's not working and not given an error.

The formula is =TEXT(D7, "mmm") and the result is "mmm".

I also tried (The excel way to handle dates in different languages):

=TEXT(D7, "[$-409]mmm") and the result is "mmm".

I've tested different date functions and they all working. (Sum(), month(), changing data format, etc).

Version: Excel 365

As it works in my pc, does anyone have any idea what might be causing the error?

score 1 · Answer 1 · answered Aug 07 '18 at 16:02

1

First - The mmm should be MMM.

Then, the fact that the PC is in Russian is a bit irrelevant. What matters is the installation language of Excel.

You may try the following

write 43319 on range A1;
then write this formula =TEXT(A1,"MMM"), using the English M and not the Cyrillic ones. Although they look quite the same, they are different.
it should return Aug as the month;

answered Aug 07 '18 at 16:02

Vityata

42,633
8
55
100

If I copy `ММ` directly out of the RU-RU docs and test the character code, I come up with ascii 63 (unicode 1052). Not what I would expect from an EN-US M (ascii 77). – Aug 07 '18 at 16:18
@Jeeped - if you copy any of these words - `=ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГ")` you would get 63. Guess you are using something like this - `?Asc(Range("A1"))`. https://stackoverflow.com/questions/25838639/character-looks-like-ascii-63-but-isnt-so-i-cant-remove-it – Vityata Aug 07 '18 at 16:21
1

No, just CODE(...) and UNICODE(...) on the worksheet. – Aug 07 '18 at 16:22
@Jeeped - yeah, the built-in formulas are faster. – Vityata Aug 07 '18 at 16:25
Sorry, for taking too long to answer. But it didn't work as well. I've tried to use the number format stead date, but the result is always the same "MMM". – Inacius Aug 14 '18 at 11:17

score 0 · Answer 2 · edited Aug 14 '18 at 12:22

I've found a way to fix it creating a new formula in VBA and it worked great.

Press Alt+F11 (to open the VBA editor) Then Click the menu item Insert > Module In the new VBA module, enter the following:

Public Function FMT$(ByVal Value, ByVal strFormat)
    FMT = VBA.Format$(Value, strFormat)
End Function

To use this, simply type =FMT(A1, "MMM") instead of =TEXT(A1, "MMM").

Credits to https://superuser.com/questions/730371/how-to-prevent-excel-to-use-the-os-regional-settings-for-date-patterns-in-formul

Excel TEXT() Formula Getting the Month returning "mmm"

2 Answers2