我导入了一个CSV文件,其中包含两列长日期。这些日期采用美国Aug/28/2013
格式,我希望它们采用标准的英国dd/mm/yyyy
格式。
我曾尝试将单元格格式化为美国日期,然后将其转换为仅限数字的格式,以及"日期格式"框中的各种其他排列,但没有成功。
有人能帮我摆脱这些糟糕的美国约会吗?
另一个不使用公式的解决方案:
- 选择相关列
- 选择数据→文本到列
- 选择"Delimited"(分隔),然后单击"Next"(下一步)
- 取消选中所有分隔符,然后单击"下一步"
- 选择数据列格式"Date:MDY",然后单击完成
日期现在应该转换为英国日期。
问题是,当美国日期小于13时,Excel可以将其解析为英国日期。发生这种情况时,Excel会将其转换为本地化的英国序列号(日期)。
因此,2014年3月19日显然是美国的3月19号。然而,2014年3月5日是不明确的,因此Excel将其解析为当地日期格式为3月5号,而不是美国5月3日。任何公式都必须检查Excel是否将美国日期存储为英国日期。英国日期将以数字形式存储在Excel中。
=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)))
(对于单元格A2中的US日期,PC日期为dd/mm/yy)。
如果ISNUMBER
为true,则美国日期看起来像英国日期,Excel已将其序列化为数字。因此可以将日期格式化为文本,然后再次返回日期。Note day被传递给第一个DATE函数的month参数以执行转换。如果ISNUMBER
为false,则其存储为字符串,因为Excel不会转换大于12个月的日期字符串。因此,使用字符串函数将其拆分为DATE函数。
我假设您收到的日期被格式化为文本,简单地将其格式化为日期不会改变任何事情。您可以在日期运行以下公式:
=(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&"-"&LEFT(A1,FIND("/",A1)-1)&"-"&RIGHT(A1,4))*1
如果你得到了数字,你只需要将其格式化为dd/mm/yyyy
,它应该很好。
我尝试了一些其他建议,但似乎都不适用。在我的情况下,我以M/d/yyyy hh:mm:ss的形式导入美国日期。如果你不介意在你的电子表格中使用一些VBA,那么下面的函数为我完成了任务:
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
谢谢https://stackoverflow.com/users/845584/peterx指出-您需要在VBA代码模块中创建函数才能使用此技术。
只需在电子表格公式中使用即可,例如=USDate(A2)
与此相关,以下简单公式有助于从更改日期
"MM/DD/YYYY"
进入
"DD/MM/YYYY"。
=VALUE(TEXT(B2,"mm/dd/yyyy"))
我们可以用这个更简洁的公式两全其美:
=IF(ISNUMBER(A2),VALUE(TEXT(A2,"mm/dd/yyyy")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2)))
找不到更短的了。
我还有一个问题,因为不知何故,原始数据应该被读取为数字,但事实并非如此。因此,我用最后一种情况更新了公式:
=IFERROR(IF(ISNUMBER(A2),VALUE(TEXT(A2,"mm/dd/yyyy")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID和US在同一列。我找到了一个有效但不雅的解决方案:
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.
Step4在其他地方也有人提出过,但它本身并没有为我做到。我希望将这些步骤结合到一个宏观中,但到目前为止还没有成功。
我找不到最常见的工作答案,对我来说有效的过程是:
对于日期10/04/2018 11:49:20
,右键单击单元格和"设置单元格格式"、"编号"选项卡,然后选择"自定义"类别,然后选择mm/dd/yyyy hh:mm
。
假设您以字符串开头(而不是仅格式化为US格式的内部excel日期号,这是一个简单的解决方案),有人能告诉我为什么这种方法不起作用吗?
使用DATEVALUE/TIMEVALUE函数将其转换为excel内部格式化的日期号(您可能需要MID()字符串,以防前后有多余的位)。
只需确保您的区域设置与输入日期格式匹配(否则DATEVALUE将失败,您将得到#VALUE错误)。
然后将单元格格式设置为所需的显示方式(自定义格式,例如"dd/mm/yyyy hh:mm:ss")。
如果您还想更改时区,可以在内部Excel Excel格式的日期编号中添加(小时/24)。
上面看起来非常复杂!我不明白为什么任何国家都应该采用非连续日期格式!假设单元格A1中有一个美国格式的日期(mm/dd/yy
)。要像在英国那样将其转换为dd/mm/yy
格式,只需执行以下操作:
=CONCATENATE(MID(A1,4,2),"/",MID(A1,1,2),"/",MID(A1,7,2))
这当然适用于LibreOffice,我希望也适用于Excel。