如何在 Excel 中将 DD.MM.YYYY 转换为日期格式 MM / DD / YYYY
本文最后更新于 80 天前,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

excel日期格式转换

前言

最近在处理数据时遇到一个问题,将文本格式的时间转换成 MM / DD / YYYY 的形式。不知道是原始数据导出带来的问题还是其他原因,怎么转换都不成功,最终在网上找到了一个解决方案,现分享出来。

方法

使用公式将 DD.MM.YYYY 转换为 DD / MM / YYYY

在某些情况下,您可以将 dd.mm.yyyy 直接转换为 dd / mm / yyyy 格式,下面的公式可能会对您有所帮助。

选择要转换的日期旁边的空白单元格,输入此公式

=SUBSTITUTE(A6,".","/")

并将自动填充手柄拖到要应用此公式的单元格上。

doc-convert-date-format-1

小技巧:在上面的公式中,A6 是您想要转换的日期,您可以根据需要进行更改。

使用公式将 DD.MM.YYYY 转换为 MM / DD / YYYY

如果要将日期格式 dd.mm.yyyy 转换为标准日期格式 mm / dd / yyyy,则可以应用以下公式。

  1. 在您使用的日期(例如 B6)旁边选择一个空白单元格,输入此公式
=(MID(A6,4,2)&"/"&LEFT(A6,2)&"/"&RIGHT(A6,2))+0

将填充手柄拖到要使用此公式的单元格上。

doc-convert-date-format-2

  1. 现在,您可以看到一个由五位数组成的数字列表,保持选中状态,将其格式化为日期格式。

doc-convert-date-format-3

然后,您可以看到非标准日期已转换为标准日期。

doc-convert-date-format-4

总结

参照下图进行转换

doc-convert-date-format

参考网址:https://zh-cn.extendoffice.com/documents/excel/3784-excel-convert-dd-mm-yyyy-to-date-format-dd-mm-yyyy.html

版权归属: E家之长
本文链接: https://www.5iehome.cc/archives/excel-change-date-type.html
许可协议: 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论

  1. Windows Edge 108.0.1462.46
    2月前
    2023-4-14 11:55:17

    I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.

  2. Windows Edge 108.0.1462.46
    1月前
    2023-5-06 22:32:19

    After reading your article, it reminded me of some things about gate io that I studied before. The content is similar to yours, but your thinking is very special, which gave me a different idea. Thank you. But I still have some questions I want to ask you, I will always pay attention. Thanks.

  3. Windows Edge 108.0.1462.46
    4周前
    2023-5-11 16:51:03

    At the beginning, I was still puzzled. Since I read your article, I have been very impressed. It has provided a lot of innovative ideas for my thesis related to gate.io. Thank u. But I still have some doubts, can you help me? Thanks.

  4. Windows Edge 108.0.1462.46
    4周前
    2023-5-13 13:11:28

    At the beginning, I was still puzzled. Since I read your article, I have been very impressed. It has provided a lot of innovative ideas for my thesis related to gate.io. Thank u. But I still have some doubts, can you help me? Thanks.

  5. Windows Edge 108.0.1462.46
    2周前
    2023-5-28 1:16:07

    The point of view of your article has taught me a lot, and I already know how to improve the paper on gate.oi, thank you. https://www.gate.io/ar/signup/XwNAU

  6. Windows Edge 108.0.1462.46
    1周前
    2023-5-28 22:01:36

    The point of view of your article has taught me a lot, and I already know how to improve the paper on gate.oi, thank you. https://www.gate.io/tr/signup/XwNAU

发送评论 编辑评论


上一篇
下一篇