带毫秒的DateTime格式在Excel网页版做散点图的日期显示问题

核心思路 #

通过文本函数将特殊格式的日期时间转换为Excel可识别的日期时间数值，再基于转换后的数据制作散点图。

操作步骤 #

假设原始数据中，日期时间在A列，数值在B列（示例数据需先按空格拆分到两列，可通过「数据」选项卡的「分列」功能，以空格为分隔符拆分）：

1. 转换日期时间格式
   在空白列（如C1）输入公式：

   =DATEVALUE(SUBSTITUTE(LEFT(A1,10),".","/")) + TIMEVALUE(SUBSTITUTE(MID(A1,12,12),",","."))
   

   下拉填充到所有行。

   • 原理：先用SUBSTITUTE把日期部分的.替换为/，通过DATEVALUE转为日期值；再把时间部分的毫秒分隔符,替换为.，通过TIMEVALUE转为带毫秒的时间值，两者相加得到完整的日期时间数值。

2. 转换数值格式
   在空白列（如D1）输入公式：

   =SUBSTITUTE(B1,",",".")
   

   下拉填充到所有行，把欧洲式逗号小数点转为Excel识别的点小数点。

3. 制作散点图

   • 选中C列（转换后的日期时间）和D列（转换后的数值）
   • 点击「插入」选项卡，选择「X-Y散点图」
   • 生成的散点图X轴将正确显示2023年的日期时间，不会出现1900年错误。

示例验证 #

针对给出的示例数据，转换后C1单元格值为2023/8/22 17:44:08.847，对应Excel内部数值45165.7389912847，散点图X轴会按实际时间顺序展示。

内容的提问来源于stack exchange，提问作者Tiago Colvara Faria