从美国格式转换为英国格式 [英] 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 的数字都会引发错误.

感谢您对此的任何帮助.这让我崩溃.

解决方案

use:

=TEXTJOIN("/",,FILTERXML("<a><b>"&SUBSTITUTE(LEFT(T​​EXT(A1,"d/m/yy")&;",FIND(",TEXT(A1,"d/m/yy")&quot;")-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屋！