Google Sheets中ET时间字符串转数值与NOW()计算时间差的方法

核心逻辑 #

Google Sheets 里的日期时间本质都是数值：整数代表距离1899年12月30日的天数，小数代表当天时分秒占1天的比例，只要把文本格式的时间字符串转成合法的日期时间数值，就能直接和NOW()做减法，精度天然支持到秒级，不需要额外做复杂的字符串拆分。 #

操作步骤 #

• 清洗文本转可计算时间值
  假设你的YYYY-MM-DD HH:MM:SS ET格式字符串存在A1单元格，先用公式移除末尾的ET标记，再把文本转成数值型时间：

  =--TRIM(SUBSTITUTE(A1,"ET",""))
  

  公式各部分作用：
  • SUBSTITUTE(A1,"ET","") 删掉字符串里的ET字符
  • TRIM() 清理删除后残留的多余空格
  • 开头的双负号--把格式合规的时间文本强制转成数值型的日期序列值
    转换后如果单元格显示为一串数字，手动把单元格格式调整为「日期时间」就能看到正常的时间显示。

  必做前置检查：点顶部菜单「文件」-「设置」，在常规选项卡下把文件时区设置为和ET（美国东部时间）匹配的时区（比如GMT-05:00 东部时间-纽约），否则NOW()取的是文件设置时区的当前时间，和ET时间会存在时区偏差，计算结果不准。

• 计算基础时间差
  转换完成后直接做减法即可，得到的结果是以「天」为单位的十进制时间差，自带秒级精度：

  =NOW() - --TRIM(SUBSTITUTE(A1,"ET",""))
  

  举个例子，结果返回0.04236就代表两个时间相差约1小时1分钟，和示例值2022-06-01 11:01:05 ET的计算逻辑完全匹配。
• 按需转换单位或显示格式
  在基础差值上乘对应系数，就能得到不同单位的时间差：
  • 转小时单位：差值 * 24，示例公式 =(NOW() - --TRIM(SUBSTITUTE(A1,"ET","")))*24
  • 转分钟单位：差值 * 1440（即24*60），示例公式 =(NOW() - --TRIM(SUBSTITUTE(A1,"ET","")))*1440
  • 转秒单位：差值 * 86400（即246060），示例公式 =(NOW() - --TRIM(SUBSTITUTE(A1,"ET","")))*86400
    如果需要显示为hh:mm:ss格式的时长，直接把结果单元格的格式设置为「时长」，或自定义单元格格式为[h]:mm:ss即可——加方括号是为了让小时数超过24时不会自动进位为天，符合时长的显示习惯。

排错提示 #

如果转换后显示#VALUE!错误，先检查原字符串里的时间值是否合法（比如月份大于12、日期超过当月天数、时分秒数值大于59），只要时间部分格式符合YYYY-MM-DD HH:MM:SS的规则，上述公式都能正常转换。

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