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]

Norie,

Now I am really sounding like a novice as this is the second thing in one day that I have never heard of. I read up on this and I like it but my office will not allow us to download or install anything not approved.

I like the idea and thank you very much for your creative thinking!

 

[Rick Rothstein]

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.

If all you need is the time value from it, then you can use this formula...

=0+TRIM(RIGHT(A1,5))

If you want the entire date/time value, and if the months and days are always two digits long (leading zeroes added where necessary), then you can use this formula...

=0+REPLACE(REPLACE(REPLACE(A1,5,1,"/"),8,1,"/"),11,1,"")

If you cannot be sure of the leading zeroes on the months/days less than 10, then you should be able to use this formula...

=0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"年","/"),"月","/"),"日","")

 

[jtakw]

Hi,

I can guarantee that 年 is Year, 月 is Month, and 日 is Day, I read, write, and speak Chinese.
Have not tested, but I would think Rick's formulas would work.

 

[jtakw]

Just tested, Rick's formula returned the correct results:

	A	B	C
2017年11月22日 10:37
2017年1月22日 10:38
2017年12月2日 10:39

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]11/22/17 10:37 AM[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1/22/17 10:38 AM[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]12/2/17 10:39 AM[/TD]

</tbody>

Sheet12​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"年","/"),"月","/"),"日","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]