Excel中2021-11-23T23:49:34.000+0000类日期时间格式如何转换

你遇到的是ISO 8601标准的UTC时间字符串，Excel默认不会将其识别为可运算的日期时间类型，因此直接修改单元格格式无效，可根据你的数据量选择以下任意方法处理：

方法1：查找替换快速转换（适合所有数据后缀统一的场景） #

• 选中所有需要处理的时间单元格区域
• 按Ctrl+H调出查找替换窗口：
  1. 查找内容输入T，替换为输入一个半角空格，点击「全部替换」
  2. 查找内容输入.000+0000，替换为留空，点击「全部替换」
• 此时Excel会自动将文本识别为标准日期时间值，右键选中区域 → 「设置单元格格式」→ 「自定义」，类型输入mm/dd/yyyy hh:mm:ss即可得到你需要的显示效果，转换后的内容支持直接做日期时间运算。

方法2：公式转换（适合需要保留原始数据的场景） #

假设原始时间数据在A1单元格，在空白单元格输入以下公式：
=--SUBSTITUTE(LEFT(A1,19),"T"," ")
公式说明：

• LEFT(A1,19)提取前19位字符，自动丢弃末尾的毫秒和时区后缀
• SUBSTITUTE把字符中间的T替换为空格
• 开头的--用于将文本格式的时间字符串转为Excel可识别的日期时间序列值
• 公式下拉批量应用后，同样设置单元格自定义格式为mm/dd/yyyy hh:mm:ss即可。

方法3：Power Query批量转换（适合多工作表、超大量数据场景） #

• 点击「数据」选项卡 → 「获取数据」→ 「自工作簿」，选择当前需要处理的Excel文件，勾选所有包含目标数据的工作表，批量导入Power Query编辑器
• 选中时间数据所在列，点击「转换」选项卡 → 「数据类型」→ 选择「日期/时间/时区」，系统会自动识别ISO格式的时间字符串
• 再次修改该列数据类型为「日期/时间」，即可自动丢弃毫秒、时区信息，得到标准日期时间值
• 点击「关闭并上载」即可将所有工作表处理后的数据批量导回Excel，后续只要刷新就能自动处理新增的同格式时间数据。

注意：如果转换后单元格显示为一串数字，属于正常现象，是Excel日期时间对应的底层序列值，仅需重新设置单元格自定义格式即可正常显示。

内容的提问来源于stack exchange，提问作者drymolasses