![让我的 Excel 像其他人一样解析 CSV 中的日期](https://linux22.com/image/1610190/%E8%AE%A9%E6%88%91%E7%9A%84%20Excel%20%E5%83%8F%E5%85%B6%E4%BB%96%E4%BA%BA%E4%B8%80%E6%A0%B7%E8%A7%A3%E6%9E%90%20CSV%20%E4%B8%AD%E7%9A%84%E6%97%A5%E6%9C%9F.png)
在工作中,我经常在 Excel 365 中打开一些数据,这些数据都是一些我无法控制的奇怪 CSV 格式。以下是示例:
"Call-off","Receiver ext.","Plant","Status","Supplier ext.","UnPt","Line station","Part family","Production No","Sequence no.","Series/Model","Call-Off date","Trig.type","EDI Arrival","No. old","No. new","SubDel.","Receiver ID","Trailer","File-No.","Created","Date Creation","Last change","Timestamp last Update"
"2238575","=""8010""","=""T21""","=""95""","=""18519280E""","=""*""","=""*"""," ","=""0006459842""","=""20191119T039""","=""16716112""","11/20/2019 12:04 AM","=""IB""","11/20/2019 12:00 AM","6443","6444","=""0""","=""MBUSI"""," ","43889667","=""AMSSTIN""","11/20/2019 12:07:54 AM","=""SSTIFILE""","11/20/2019 12:07:54 AM"
"2238574","=""8010""","=""T21""","=""90""","=""18519280E""","=""*""","=""*"""," ","=""0006459842""","=""20191119T039""","=""16716112""","11/20/2019 12:04 AM","=""IB""","11/20/2019 12:00 AM","6442","6443","=""0""","=""MBUSI"""," ","43889666","=""AMSSTIN""","11/20/2019 12:07:54 AM","=""SSTIFILE""","11/20/2019 12:07:54 AM"
"2238572","=""510898""","=""456""","=""90""","=""33265005""","=""56""","=""BROADCAST"""," ","=""0006459842""","=""300000162834""","=""167""","11/20/2019 12:04 AM","=""SMP""","11/20/2019 12:00 AM","56386","56387","=""0""","=""SMP"""," ","43889664","=""AMSSTIN""","11/20/2019 12:07:53 AM","=""SSTIFILE""","11/20/2019 12:07:53 AM"
我需要添加对数据中的日期和时间进行运算的公式(参见示例数据中的最后一列)。问题是,我的 Excel 将日期视为字符串。这导致我插入一个表格,然后执行数据 -> 来自表格/范围,这将打开一个新工作表,其中删除了虚假公式,只留下值(并销毁了前导零的数字)。我的另一种选择是插入辅助列并从日期字符串中解析出日期。
问题是,每当我的同事按照我写的说明和我做同样的事情时,他们得到的日期都是正确的日期。这是对同一数据进行操作。更奇怪的是,我在两台不同的机器上都遇到过这个问题,但我从未见过我的同事遇到过这种情况。
这使我相信 Excel 中一定存在某些设置改变了我的情况。我怎样才能使我的 Excel 像我的同事的一样工作?
注意:我不是在问如何解决这个问题,因为我已经知道如何解决这个问题。我是在问为什么会发生这种情况以及/或者我如何才能在不诉诸变通方法的情况下防止这种情况发生。
答案1
(反对者:你们不觉得羞耻吗?)
要将列转换为日期格式:
- 选择列(将鼠标悬停在标题上,出现向下箭头时单击)
- 在主页选项卡中,单击格式 > 格式化单元格...
- 在对话框中,选择“数字”选项卡,选择“日期”,并将“区域设置”设置为“英语(美国)”
- 单击“确定”。
该列现在应为日期类型。
如果您想验证这一点,请在空单元格中使用公式
=VALUE(AS2)
并将其拖动到整个列上。
我的发现:
Excel 文档即使没有错误,也是误导性的。单元格的日期格式显然仅用于显示。Excel 将使用操作系统函数解析字符串,因此拥有正确的日期格式至关重要在 Windows 中。
在控制面板 > 区域,格式选项卡,我已将“格式:”设置为“英语(美国)”。如果没有此设置,Excel 将不会接受美式日期。
我选择了整列,按Ctrl+1
进入“格式化单元格”对话框,选项卡“数字”,并将格式设置为“自定义”,值为[$-en-US]m/d/yyyy h:mm AM/PM
。
对于我使用函数添加的 AU 列来说,这已经足够了,
=VALUE(AS2)
现在可以正确地将 AS 列分析为数字,从而给出日期时间数值:
Excel 的另一个特点是,尽管现在单元格是数字,但单元格仍然保持左对齐。如果双击单元格内部,然后单击退出,单元格会突然显示为右对齐。想想看。
答案2
在 AU2 中使用它将 AS2 转换为日期;
=DATEVALUE(MID(AS2,4,2)&"/"&MID(AS2,1,2)&"/"&MID(AS2,7,4))
发生这种情况的原因是日期不是美国格式。需要更改源数据。