CODEWITHSUNDEEP
excelexcel-formula
Michi
Michi

Reputation: 5481

Date-Formatting in TEXT formula independent from used language in Excel

           A                 B                          
1      2020-01-01        2020-01-01     =TEXT(A1,"YYYY-MM-DD")
2
3

In my spreadsheet I have a date written in Cell A1 and change it to a TEXT format with the formula in Cell B1.
All this works fine as long as I use the English version of Excel.

Now, a coworker in Poland needs to use the file in a polish Excel version.
Once he opens the file the formula in Cell B1 changes to:

=TEKST(A2;"YYYY-MM-DD")

This formula gives back an error because in Poland instead of YYYY-MM-DD you need to use RRRR-MM-DD.
Therefore, I am wondering if it is somehow possible to make this text formatting international so no matter in which language your Excel is set up the formula is working?

Upvotes: 10

Views: 2972

Answers (4)

Julien Kronegg
Julien Kronegg

Reputation: 5271

You can format your Excel date in YYYY-MM-DD date format using the following (assuming your date is in cell A1, and Excel functions are in English):

=YEAR(A1) & "-" & RIGHT("0"&MONTH(A1);2) & "-" & RIGHT("0"&DAY(A1);2)

The advantages:

  • works independently of your current locale settings (e.g. works with Excel in English and swiss-french locale fr-CH)
  • requires no macro

The disadvantages:

  • it's not very readable

Upvotes: 3

Greedo
Greedo

Reputation: 5543

Another option with no macros is to use FORMULATEXT:

In a random cell Z5

=TEXT(123, "foo")

Then in your desired cell C1

=TEXT(A1,IF(ISERROR(SEARCH("TEKST", FORMULATEXT(Z5)), "YYYY", "RRRR") & "-MM-DD")

This works because the formula of the random cell will be updated to say TEKST in polish and you can just spot that happening. Obviously the other method is more robust to any country but this requires no settings to be changed.

Bigger:

=TEXT(
  A1,
  IF(
    ISERROR(
      SEARCH(
        "TEKST",
        FORMULATEXT(Z5)
      ),
      "YYYY",
      "RRRR"
    ) & "-MM-DD"
  )

Upvotes: 0

JvdV
JvdV

Reputation: 75990

If the only problem is sharing it with your Polish co-worker, then you can simply use the international placeholder "e" to replace "YYYY":

=TEXT(A1,"e-MM-DD")

Now, while this would work fine for your co-worker in Poland, there are countries where "m" and "d" would also need replacement. If in general we need to return the locale "Y", "M" or "D" equivalent you could create three named formulas using the name manager:

  • 1st: Create name called YT and refer to =INDEX(GET.WORKSPACE(37),19)
  • 2nd: Create name called MT and refer to =INDEX(GET.WORKSPACE(37),20)
  • 3rd: Create name called DT and refer to =INDEX(GET.WORKSPACE(37),21)

Now you can use a reference to these names through:

=TEXT(A1,REPT(YT,4)&"-"&REPT(MT,2)&"-"&REPT(DT,2))

NOTE: Depending on which version of Excel you use it could be necessary to:
Enable Excel 4.0 macros when VBA macros are enabled in the Trust Center

Upvotes: 9

Jeorje
Jeorje

Reputation: 5

The solution is actually simple, surprisingly so.

Whether specifying a formal Custom Format, or specifying a format inside a TEXT() (or TEKST() function), you just add the country-specific code for interpreting the formatting string. So, if a US-Excel version person is writing the formula, and is presumably most conversant in the US English strings to use, and especially if expecting the users to be US-Excel version users, he would write it as:

=TEXT(A1,"[$-en-US]YYYY-MM-DD")

Every version of Excel will then use the US English set of formatting string variables in interpreting the formatting string that follows the [$-en-US]. Since the string is perfectly valid in US English Excel, it will form the date properly and "textify" it as demanded. No error will be produced.

I cannot test a nuance here, that of whether Excel of a different language version will insert itself into the middle here and take the date as the string formats it, and map it to the particular language's names for months, but that is not asked for in the poster's question so... I have the feeling it will not and so one might need to do further work to make the Polish user happiest. If month names were used instead of month numbers.

But the key element of generating a correct numeric date display with no possibility — none at all — of an #ERROR! result rendering things ugly and useless will absolutely be accomplished with no great effort at all.

Upvotes: -2

Related Questions