如何在Excel中将日期时间字符串转为可计算对象并保留原格式

核心原因 #

你之前设置自定义格式无效，是因为粘贴的20221025133811是文本类型，而Excel的自定义格式只对「数值/日期时间类型」生效，文本不会被格式规则改变。所以得先把文本转成日期时间值，再设置显示格式。

方法一：公式转换（适合单/少量单元格） #

假设你的日期时间字符串在A1单元格：

• 在空白单元格（比如B1）输入公式：

  =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),RIGHT(A1,2))
  

  公式解释：用LEFT/MID/RIGHT拆分字符串的年、月、日、时、分、秒，再用DATE和TIME函数组合成日期时间值，两者相加得到完整的日期时间对象。
• 选中B1单元格，右键选择「设置单元格格式」→「数字」→「自定义」，在类型框输入yyyyMMddhhmmss，点击确定。
• 此时B1单元格显示20221025133811，且是可参与减法运算的日期时间值。

方法二：数据分列（适合批量处理） #

如果有大量这类字符串需要转换：

• 选中所有待处理的单元格区域。
• 点击顶部菜单栏「数据」→「分列」：
  • 第一步：选择「分隔符号」，点击下一步；
  • 第二步：取消所有分隔符选项（勾选框都去掉），点击下一步；
  • 第三步：在「列数据格式」里选择「日期」，下拉菜单选YMD（年-月-日），点击完成。
• 此时选中的单元格已经转为日期时间值，再按方法一的步骤设置自定义格式yyyyMMddhhmmss即可。

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