CODEWITHSUNDEEP
excelexcel-formula
Inacius
Inacius

Reputation: 23

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

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?

Upvotes: 0

Views: 2619

Answers (2)

Inacius
Inacius

Reputation: 23

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

Upvotes: 0

Vityata
Vityata

Reputation: 43595

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;

Upvotes: 1

Related Questions