1

I have searched a lot the web but I couldn't find a solution on the following problem:

In Cell A1, I have inserted a date.

For example 42736, (which is 1 January 2017).

In Cell B1, I want to display the following: 

"The result is: January 2017"

In Cell B1, the formula is: 

="The result is: " &TEXT(A1;"MMMM YYYY")

However, because my computer has German regional format, I have the following two issues:

1) The name of the month is in German and not in English

2) It does not understand the YYYY.

For the second issue, I have found a solution of creating the following UDF:

' standard date format regardless of excel language
Public Function STDTEXT$(ByVal Value, ByVal strFormat)
    STDTEXT = VBA.Format$(Value, strFormat)
End Function

Question

Is there any solution to overcome the first obstacle?

I want to return the month name in English, regardless of the local regional settings of the user.

Regards

Thanasis

T.M.
  • 9,436
  • 3
  • 33
  • 57
Thanasis
  • 695
  • 1
  • 4
  • 17
  • I'd prefer not to change my whole system over to multiple regional languages for testing so could you try `=text(a1, "[$-x-sysdate]mmmm yyyy")`. This uses the universal language format mask that comes from cell formatting. –  Apr 21 '17 at 13:28
  • You might also try this cell formatting mask in B1: `\T\h\e\ \r\e\s\u\l\t\ \i\s\: [$-x-sysdate]mmmm yyyy` and simply put `=A1` in B1. –  Apr 21 '17 at 13:31
  • Two issues with above suggestion: assuming e.g. German language settig it's necessary to use as well the localized form `sysdat` instead of `sysdate`, JJJJ instead of YYYY, i.e. `="=TEXT(A1; ""[$-x-sysdat]MMMM JJJJ"")"` as to accept the localiszed result, e.g. "...: Januar 2017" – T.M. Sep 09 '19 at 06:53

3 Answers3

1

You can use create external table D1:E12:

1   January
2   February
3   March
4   April
5   May
6   June
7   July
8   August
9   September
10  October
11  November
12  December

And then use:

="The result is: "&VLOOKUP(MONTH(A1),$D$1:$E$12,2,FALSE)&" "&YEAR(A1)
zipa
  • 27,316
  • 6
  • 40
  • 58
  • I thought about that solution but I an looking for a more universal solution that it will work for MMM too. – Thanasis Apr 21 '17 at 12:51
  • Clever idea, but still it's not possible that Excel i SO MUCH translated that format string are different across locales – Luca Regazzi Sep 04 '18 at 07:40
  • Posted a (late) answer how to get *English month names* independant from regional settings and based on a single range reference only instead of additionally using localized format patterns :-) – T.M. Sep 08 '19 at 06:35
0

For the first issue I have used following workaround solution

In separate field/sheet created Language Check:

Cell A1: =NA() - just inserts #N/A in the cell

Cell A2: =FORMULATEXT(A1) - converts =NA() function into plain text

Cell A3: =IF(A2="=NA()", "English", IF(A2="=NV()", "German", "Other")) - while opening a file Excel automatically converts functions into installed language, and English NA becomes NV (nicht verfügbar), and cell shows German.

Further in date cell B1 I used =IF($A$3="English", "DD.MM.YYYY", IF($A$3="German", "TT.MM.JJJJ", "ERROR (Language must be English or German"))

(Although this solution works if the user has the same language in Windows and Excel).

Community
  • 1
  • 1
Cibron Nethis
  • 1
  • 1
0

Example call via UDF

This approach demonstrates how to get English month names independant from regional settings and based on a single range reference only instead of additionally using localized format patterns.

With cell formula 

="The result is " & endate(A1)

one gets the English month name of a date value in A1, independant from regional setting:

"The result is: January 2017"

User defined function EnDate()

Function EnDate$(dat#)
' Purpose: return English month name plus year
' Note:    international codes ("patterns") at https://msdn.microsoft.com/en-us/library/dd318693(VS.85).aspx
  Const EN = 409
  EnDate = Application.Text(DateSerial(6, Month(dat), 1), "[$-" & EN & "]mmmm") & " " & Year(dat)
End Function

Related link

How to create a calendar Input in VBA Excel

T.M.
  • 9,436
  • 3
  • 33
  • 57