从美国转换为英国格式 [英] Convert from US to UK format

问题描述

如何将以下内容修改为D/M/YYYY?

  8/7/20 6:20 PM20/8/17下午4:24 

当前为M/D/YY

这是我无济于事的事情:

1. = VALUE(LEFT(A2，FIND("，A2,1)-1))-这是为了提取日期部分(因为excel将其视为文本)
2. 试图使用文本到列"将日期转换为D/M/Y，但这不起作用.Excel将中间数字视为日期，因此任何超过12的数字都会引发错误.

谢谢您的帮助.真伤了我.

解决方案

使用:

  = TEXTJOIN("/" ,, FILTERXML("a"> b")"SUBSTITUTE(LEFT(TEXT(A1，d/m/yy))"；"，FIND("，"TEXT(A1"，"d/m/yy")&)-1)"，"/"，"/b"；< b")&"//b"/a"，"//b [" {2,1,3}&"]]))+ MID(TEXT(A1，"\ hh:mm AM/PM")，FIND("，TEXT(A1，"\ hh:mm AM/PM"))+ 1,99) 

，然后根据需要格式化输出.

注意:我是美国用户，所以我不得不在演示中将其撤消.

另一注:如果一个以美国为基础的英国日期，则将 d/m/yy 更改为 m/d/yy ，它将起作用.

How do I modify the following to be D/M/YYYY?

8/7/20 6:20 PM

8/17/20 4:24 PM

Currently it is M/D/YY

Here is what I have done to no avail:

1. =VALUE(LEFT(A2,FIND(" ",A2,1)-1)) - This is to extract the date portion (because excel sees it as text)
2. Tried to convert the date to a D/M/Y using 'text to columns' but that does not work. Excel sees the middle number as the date so anything over 12 and it throws an error.

Thank for any help on this. It's breaking me.

解决方案

use:

=TEXTJOIN("/",,FILTERXML("<a><b>"&SUBSTITUTE(LEFT(TEXT(A1,"d/m/yy")&" ",FIND(" ",TEXT(A1,"d/m/yy")&" ")-1),"/","</b><b>")&"</b></a>","//b["&{2,1,3}&"]"))+MID(TEXT(A1,"\ hh:mm AM/PM"),FIND(" ",TEXT(A1,"\ hh:mm AM/PM"))+1,99)

and then format the output as desired.

Note: I am US based so I had to reverse it in the demo.

Another Note: If one is US based dealing with UK dates then change the d/m/yy to m/d/yy and it will work.

这篇关于从美国转换为英国格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！