How do I convert foreign dates?

[USAMax]

Every now and then I get what I believe to be a Chinese date. Any suggestions on how to convert this?

2017年11月22日 10:37

I need to keep the time.

 

[USAMax]

Please note I read the character after 2017 as Ascii 63 but when I do an Instr for asc(63) it does not find it.

 

[Norie]

All of the characters between the digits have ASCII value of 63 which suggests to me they are actually Unicode characters.

If you use the worksheet function UNICODE you'll get their 'real' character code.

 

[shg]

Assuming the numbers are month and day and the Chinese characters are spurious (dubious, huh?),

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]2017年11月22日 10:37[/td][td="bgcolor:#E5E5E5"]

11/22/2017 10:37​

[/td][td]B2: =DATE(LEFT(A2, 4), MID(A2, 6, 2), MID(A2, 9, 2)) + RIGHT(A2, 5)[/td][/tr]
[/table]

 

[shg]

Huh -- the characters are U+5E74 ("year"), U+6708 ("month"), and U+65E5 ("day")

 

[USAMax]

shg,

I have been doing this a while but I have no idea what you mean. Currently the string value in in a variable called strTemp.

I have thought about using something like your formula but it will always assume a two digit month and two digit day.
B2: =DATE(LEFT(A2, 4), MID(A2, 6, 2), MID(A2, 9, 2)) + RIGHT(A2, 5)

 

[shg]

The formula returns an Excel date/time value. You can format it however you like.

In VBA,

  Dim s As String
  Dim t As Date
  
  s = Range("A2").Value2
  
  t = DateSerial(Left(s, 4), Mid(s, 6, 2), Mid(s, 9, 2)) + TimeValue(Right(s, 5))
  Debug.Print Format(t, "dd mmm yyyy hh:mm")

 

[Norie]

For what's it's worth, I copied that 'date' into a worksheet, copied it down, loaded it into PowerQuery and the values was automatically converted to date/time.

 

[shg]

Just reread this:

but it will always assume a two digit month and two digit day.

Indeed it does -- is that not the case?

 

[USAMax]

shg,

I changed the date as follows and it failed. I need a way that will work with one and two digit Months and Days.
2017年1月2日 10:37