0

I'm writing this question after reading How to use DATEVALUE with diffent locale setting without changing system settings didn't solve my problem.

It's about reading the correct month from a text created with a different locale. For example the dates come from US locale and I'm using GE locale. Then the months are slightly different:

US months JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC

GE months JAN, FEB, MRZ, APR, MAI, JUN, JUL, AUG, SEP, OKT, NOV, DEZ

I've seen a solution to write a different locale using TEXT function, but I wonder what is the efficient way to do it now. (I consider creating a table or array as last resort ;-)).

Thanks for your hints! +Daniel+

  • 3
    ".. from a text created with a different locale.. " so the source cell is formatted as a text or excel date value? if text, what is the format? any sample data/intended-output? – p._phidot_ Jan 23 '19 at 11:05
  • Example '03-JAN-2019 15:43' to Excel date (serial number). – user10884217 Feb 8 '19 at 10:42
0

..using TEXT function, but I wonder what is the efficient way ..

Efficient in term of?... I'm not sure whether it simpler or more complex .. I think simple is relative. What I hold on to is.. a method is efficient when we have faith (and clear understanding) in it. IMHO, it'll be more troublesome when we have doubt (in the method) added in. ( :

Assuming the source text is in A1, do :

=DATE(MID(A1,8,4),CHOOSE(MATCH(MID(A1,4,3),{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),1,2,3,4,5,6,7,8,9,10,11,12),MID(A1,1,2))+TIME(MID(A1,13,2),MID(A1,16,2),0)

Idea :

  1. use MID() to get the year/month/day/hh/ss (number&text) values
  2. use choose() n match() to "convert" the month.
  3. use date()&time() to "convert" to excel

hope it helps.

| improve this answer | |
  • OK, so I see you use an array embedded in this formula. If i't's the way to go then I'm okay with it. Just thought that there was some programmer trick like for the writing solution. – user10884217 Feb 14 '19 at 9:32
  • the closest to that I knew is datevalue() which can be applied by =DATEVALUE(MID(A1,1,11))+TIME(MID(A1,13,2),MID(A1,16,2),0) || but I really not sure will the excel date locale setting affect it's workability.. (I don't have text sample with other date locale test/verify), that's why I propose something that is confident to be working + customizable. – p._phidot_ Feb 15 '19 at 1:24
0

An approach you could take would be to note there are four months that need changed from the English abbreviation to the German one and that you can use nested SUBSTITUTE()'s (four of them) to swap out whichever arises, if one of them does, leaving the eight OK ones alone.

That gives your formula a string your version should recognize so if you then wrap that in a VALUE() function, it should have no issues converting the string to the date number for the day.

And if you format the cells however you normally would, you should be ready to roll.

However, I admit I have never used a non-English version and do not know how identically the functions are implemented relative to how they are in the English version so the VALUE() function might not work exactly like it does in the English version.

(And of course, those functions may have different names, but that's an easy one.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.