blocks|key|1995821|text|我假设您收到的日期被格式化为文本，简单地将其格式化为日期不会改变任何事情。您可以在日期上运行以下公式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1995822|=(MID(A1,FIND("/",A1)%2B1,FIND("/",A1,FIND("/",A1)%2B1)-FIND("/",A1)-1)&"-"&LEFT(A1,FIND("/",A1)-1)&"-"&RIGHT(A1,4))*1|code-block|syntax|javascript|1995823|如果你得到了数字，你只需要将它格式化为dd/mm/yyyy，它应该是好的。|offset|length|style|CODE|1995824|entityMap^0|0|0|J|A|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I'm assuming that the date you received is formatted as text and that simply formatting it as date is not changing anything. You can run the following formula on the date:

<pre><code>=(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&amp;"-"&amp;LEFT(A1,FIND("/",A1)-1)&amp;"-"&amp;RIGHT(A1,4))*1
</code></pre>

If you get numbers, you just need to format it as <code>dd/mm/yyyy</code> and it should be good.

blocks|key|1618109|text|问题是，当日期小于13时，Excel可以将美国日期解析为英国日期。当发生这种情况时，Excel会将其转换为本地化的英国序列号(日期)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1618110|因此，2014年3月19日显然是美国3月19日的日期。然而，05/03/2014是模棱两可的，因此Excel将其解析为本地日期格式为3月5日，而不是美国的5月3日。任何公式都必须检查Excel是否将美国日期存储为英国日期。英国日期将以数字形式存储在Excel中。|1618111|=IF(ISNUMBER(A2),DATE(TEXT(A2,"yyyy"),TEXT(A2,"dd"),TEXT(A2,"mm")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)%2B1,2)))|code-block|syntax|javascript|1618112|(对于单元格A2中的美国日期，PC日期为dd/mm/yy)。|1618113|如果ISNUMBER为true，则美国日期看起来像是英国日期，并且已将其序列化为数字。因此，可以将日期格式化为文本，然后再返回到日期。注意，day被传递给第一个DATE函数的month参数来执行转换。如果ISNUMBER为false，则将其存储为字符串，因为Excel不会转换大于12个月的日期字符串。因此，使用字符串函数将其拆分为DATE函数。|offset|length|style|CODE|1618114|entityMap^0|0|0|0|0|2|8|2U|8|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|W|8|@$M|X|N|Y|O|P]|$M|Z|N|10|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

The problem is that a US date can parsed by Excel as a UK date when the day is less than 13. When this happens Excel converts it to the localized UK serial (date) number. 

So 03/19/2014 is obviously a US date of the 19th of March. However 05/03/2014 is ambiguous so Excel parses it the local date format as the 5th of March, rather than the US 3rd of May. Any formula has to check if Excel has stored the US Date as a UK date. A UK date will be stored in Excel as a number. 

<pre><code>=IF(ISNUMBER(A2),DATE(TEXT(A2,"yyyy"),TEXT(A2,"dd"),TEXT(A2,"mm")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2)))
</code></pre>

(For a US date in cell A2 and PC date is dd/mm/yy).

If <code>ISNUMBER</code> is true, the US date looks like a UK date and Excel has serialized it as a number. So can format the date as text and back to a date again. Note day is passed to the month parameter of the first DATE function to perform the conversion. 
If <code>ISNUMBER</code> is false, its stored as a string as Excel doesn't convert a date string with >12 months. So use string functions to split it up for the DATE function.

blocks|key|5524182|text|与此相关，下面的简单公式对更改日期很有帮助|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5524183|5524184|+"MM/DD/YYYY“|blockquote|5524185|5524186|5524187|转到|5524188|5524189|“日/月/年”。|5524190|5524191|5524192|=VALUE(TEXT(B2,"mm/dd/yyyy"))|code-block|syntax|javascript|5524193|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|X|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|G|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|10|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|11|8|@]|9|@]|A|$]]|$1|K|3|L|5|E|7|12|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|13|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|14|8|@]|9|@]|A|$]]|$1|O|3|P|5|Q|7|15|8|@]|9|@]|A|$R|S]]|$1|T|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|U|$]]

Related to this, the below simply formula can be helpful for changing a date from 

<blockquote>
 "MM/DD/YYYY"
</blockquote>

into 

<blockquote>
 "DD/MM/YYYY".
</blockquote>

<pre><code>=VALUE(TEXT(B2,"mm/dd/yyyy"))
</code></pre>

blocks|key|4106170|text|我们可以通过这个更简洁的公式来充分利用这两个世界：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4106171|=IF(ISNUMBER(A2),VALUE(TEXT(A2,"mm/dd/yyyy")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)%2B1,2)))|code-block|syntax|javascript|4106172|找不到更短的了。|4106173|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

We can get best of both world with this more concise formula:

<pre><code>=IF(ISNUMBER(A2),VALUE(TEXT(A2,"mm/dd/yyyy")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2)))
</code></pre>

Can't find anything shorter.

blocks|key|1995902|text|对我来说还有一个问题，因为不知何故，原始数据应该被读取为数字，但它没有。因此，我用1个最终案例更新了公式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1995903|=IFERROR(IF(ISNUMBER(A2)，VALUE(TEXT(A2，"mm/dd/yyyy"))，DATE(RIGHT(A2,4)，LEFT(A2，FIND("/"，A2)-1)，MID(A2，FIND("/"，A2)%2B1，2))，DATE(RIGHT(A2,4)，LEFT(A2，FIND("/"，A2)-1)，MID(A2，FIND("/"，A2)%2B1，1))|1995904|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

There was one more issue for me, as somehow the raw data was supposed to be read as a number, but it did not. Hence, i updated the formula with 1 final case:

=IFERROR(IF(ISNUMBER(A2),VALUE(TEXT(A2,"mm/dd/yyyy")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2))),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,1)))

blocks|key|1614949|text|当日期以混合格式出现时，这可能会很棘手。英国和美国在同一列。我找到了一个有效但不优雅的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1614950|Step1)+Select+the+column+containing+the+dates+to+be+converted;

Step2)+Format,+Cells,+Text;

Step3)+Format,+Cells,+Date,+US;

Step4)+Data,+Text+to+column,+Next,+Delimited,+Next,+delete+all+delimiters,+Next,+select+format+MDY;

Step5)+Format,+Cells,+Date,+UK.|code-block|syntax|javascript|1614951|其他地方也有人建议使用Step4，但它本身并不适合我。我希望将这些步骤组合到一个宏中，但到目前为止还没有成功。|1614952|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This can be tricky when the dates in mixed format eg. UK and US in the same column. I have found an effective if inelegant solution:

<pre><code>Step1) Select the column containing the dates to be converted;

Step2) Format, Cells, Text;

Step3) Format, Cells, Date, US;

Step4) Data, Text to column, Next, Delimited, Next, delete all delimiters, Next, select format MDY;

Step5) Format, Cells, Date, UK.
</code></pre>

Step4 had been suggested elsewhere, but that on it's own didn't do it for me. I am hoping to combine these steps into a macro but no success this far.

blocks|key|4106286|text|我尝试了其他一些建议，但似乎都不适合我。在我的例子中，我以M/d/yyyy+:mm:ss的形式导入了美国日期。如果你不介意在你的电子表格中使用一些VBA，那么下面的函数可以帮我完成这项工作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4106287|Public+Function+USDate(ds+As+Variant)+As+Variant
++++Dim+sp()+As+String
++++Dim+spt()+As+String
++++Dim+spt2()+As+String

++++If+ds+=+vbNullString+Then
++++++++USDate+=+""
++++ElseIf+IsNumeric(ds)+Then+
++++++++'+Convert+numeric+US+dates+wrongly+interpreted+as+UK+i.e.+1/7/2017+as+7th+January+2017
++++++++USDate+=+DateSerial(Year(ds),+Day(ds),+Month(ds))+
++++Else
++++++++sp+=+Split(ds,+"/")+'+split+the+date+portion
++++++++spt+=+Split(sp(2),+"+")+'+split+the+time+from+the+year
++++++++spt2+=+Split(spt(1),+":")+'split+the+time+hms

++++++++USDate+=+DateSerial(spt(0),+sp(0),+sp(1))+%2B+TimeSerial(spt2(0),+spt2(1),+spt2(2))
++++End+If
End+Function|code-block|syntax|javascript|4106288|感谢https://stackoverflow.com/users/845584/peterx指出-您需要在VBA代码模块中创建函数才能使用此技术。|offset|length|4106289|只需在电子表格公式中使用它，例如=USDate(A2)|4106290|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/users/845584/peterx^0|0|0|2|19|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@$I|X|J|Y|1|Z]]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

I tried some of the other suggestions but none seemed to work for me. In my case I was importing US dates in the form M/d/yyyy hh:mm:ss. If you don't mind using some VBA in your spreadsheet then the following function did the job for me:

<pre><code>Public Function USDate(ds As Variant) As Variant
 Dim sp() As String
 Dim spt() As String
 Dim spt2() As String

 If ds = vbNullString Then
 USDate = ""
 ElseIf IsNumeric(ds) Then 
 ' Convert numeric US dates wrongly interpreted as UK i.e. 1/7/2017 as 7th January 2017
 USDate = DateSerial(Year(ds), Day(ds), Month(ds)) 
 Else
 sp = Split(ds, "/") ' split the date portion
 spt = Split(sp(2), " ") ' split the time from the year
 spt2 = Split(spt(1), ":") 'split the time hms

 USDate = DateSerial(spt(0), sp(0), sp(1)) + TimeSerial(spt2(0), spt2(1), spt2(2))
 End If
End Function
</code></pre>

Thanks for <a href="https://stackoverflow.com/users/845584/peterx">https://stackoverflow.com/users/845584/peterx</a> pointing out - you will need to create the function in a VBA code module to use this technique.

Simply use it in a spreadsheet formulae for example =USDate(A2)

blocks|key|1618203|text|我不能得到最常见的工作答案，对我有效的过程是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1618204|对于date+10/04/2018+11:49:20，右键单击单元格和“设置单元格格式”、"Number“选项卡，选择"Custom”Category，然后选择mm/dd/yyyy+hh:mm。|offset|length|style|CODE|1618205|entityMap^0|0|7|J|28|G|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]|$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|P|8|@]|9|@]|A|$]]]|I|$]]

I couldn't get the most common answer to work, the process that worked for me was:

For date <code>10/04/2018 11:49:20</code>, right-click cell and "Format Cells", "Number" tab and select "Custom" Category and then select <code>mm/dd/yyyy hh:mm</code>.

blocks|key|5524416|text|假设您从一个字符串开始(而不是一个内部excel日期数字，它只是格式化为US格式-这是一个简单的修复)，有人能告诉我为什么这个方法不起作用吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5524417|使用DATEVALUE+/+TIMEVALUE函数将其转换为excel内部格式的日期数字(如果在字符串之前或之后有额外的位，则可能需要对字符串执行中间()操作)。|5524418|只需确保您的区域设置与输入日期格式匹配(否则DATEVALUE将失败，并且您将得到#VALUE错误)。|5524419|然后设置单元格格式以按您希望的方式显示(自定义格式，例如"dd/mm/yyyy+hh:mm:ss")。|5524420|如果您还想更改时区，可以在内部Excel+excel格式的日期数字中添加(小时/24)。|5524421|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

Assuming that you start with a string (and not an internal excel date number that is just formatted as US format - which is an easy fix), can someone tell me why this method doesn't work?

Use the DATEVALUE / TIMEVALUE functions to convert it into an excel internal formatted date number (You might need to MID() the string in case there are extra bits before or after).

Just make sure that your regional settings match the input date format (otherwise DATEVALUE will fail and you will get a #VALUE error).

Then set the cell format to display the way you want it (Custom format e.g. "dd/mm/yyyy hh:mm:ss").

If you also want to change the timezone, you can add on (hours/24) to the internal Excel excel formatted date number.

blocks|key|5524491|text|上面的内容看起来非常复杂！我不明白为什么任何国家都应该采用不连续的日期格式！假设您在单元格A1中有一个美国格式的日期(mm/dd/yy)。要像在英国那样将其转换为dd/mm/yy格式，只需执行以下操作：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5524492|=CONCATENATE(MID(A1,4,2),"/",MID(A1,1,2),"/",MID(A1,7,2))+|code-block|syntax|javascript|5524493|这当然适用于LibreOffice，我希望也适用于Excel。|5524494|entityMap^0|1N|8|29|8|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

The above look impressively complex! Why any country should settle on a non-sequential date format escapes me! Say you have a US-format date (<code>mm/dd/yy</code>) in cell A1. To convert this to <code>dd/mm/yy</code> format as in the UK, just do:
<pre><code>=CONCATENATE(MID(A1,4,2),&quot;/&quot;,MID(A1,1,2),&quot;/&quot;,MID(A1,7,2)) 
</code></pre>
This certainly works in LibreOffice and I hope also in Excel.

I have imported a CSV file with 2 long columns of dates. These dates are in the US format <code>Aug/28/2013</code> and I want them to be in the standard UK <code>dd/mm/yyyy</code> format.

I have tried formatting the cells as US dates and then converting them to number-only formats, and various other permutations within the Date format box, but with no success. 

Can anyone rid me of these awful US dates please?

Excel 2010 - change US dates to UK format

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我已经导入了一个CSV文件，其中包含2个较长的日期列。这些日期是美国格式的Aug/28/2013，我希望它们是标准的英国dd/mm/yyyy格式。我尝试将单元格格式化为美国日期，然后将它们转换为纯数字格式，以及日期格式框中的各种其他排列，但都没有成功。有人能帮我摆脱这些糟糕的美国日期吗？

问Excel 2010 -将美国日期更改为英国格式
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel 2010 -将美国日期更改为英国格式EN