将时间戳 00:00:00.000 转换为总秒数和毫秒数

将时间戳 00:00:00.000 转换为总秒数和毫秒数

我遇到的问题是,我留下了一个这样的时间戳:00:02:02.300,意思是:小时 - 分钟 - 秒 - 毫秒。

并且需要拼命将其转换为总秒数。这很棘手,因为我有毫秒位,但 Excel 中既没有明确的格式,也不可能轻松地“剪掉”毫秒。在最佳情况下,我需要总秒数和毫秒数,例如 6528.32 或类似的数字。

答案1

如果您确定时间戳始终采用该格式 (HH:mm:ss.SSS),则可以使用以下 Excel 函数(前提是时间戳位于单元格 A1 中,并且其格式为文本):

=VALUE(RIGHT(A1, 6))+60*VALUE(MID(A1, 4, 2))+3600*(LEFT(A1, 2))

对于的时间戳00:02:02.300,这将给出结果122.3

如果将其格式化为日期,那就更简单了:

=A1*86400

(因为 Excel 将时间存储为一天的分数;例如 12:00:00 存储为 0.5,因为它是 24 小时的一半)

答案2

如果您00:02:02.300在单元格中有一个内容,则如果其格式为文本1,则其可能看起来与此完全相同,如果其格式为其他内容,则其可能看起来有所不同。

这个技巧很容易就能得到秒和毫秒,也就是122.300

首先,假设00:02:02.300单元格 A1 中为 。突出显示它并将其格式化为数字。它将显示一些看似无意义的内容。可能是 0.00。转到单元格 B1 并输入=A1*86400。单元格 B1 现在将包含122.3。要获取尾随零,请将 B1 格式化为数字并根据您的喜好进行调整。

为了正确给出格洛芬德尔的VALUE解决方案,公式如下

=VALUE(3600*LEFT(A1,2))+VALUE(60*(MID(A1,4,2))+VALUE(MID(A1,7,2))+VALUE(MID(A1,9,3))

我将解释一下这个公式的作用。它将单元格A1视为文本字符串,然后将其部分转换为数字,然后将它们适当地加在一起,使其成为总秒数。它有四个部分:

  1. VALUE(3600*LEFT(A1,2))
  2. VALUE(60*(MID(A1,4,2))
  3. VALUE(MID(A1,7,2))
  4. VALUE(MID(A1,9,3))

查看字符串00:02:02.300,您会发现它基本上是由:和分隔的四个数字.。这四个数字中的每一个都对应于上面的四个部分,从左到右。

VALUE(3600*LEFT(A1,2))是将00小时位置的 转换为秒。第二项处理下一个数字02,将其转换为分钟。依此类推。现在,请注意,在完整公式中,上面列表中的每个项目都是添加 +合在一起。这样你就得到了秒数和小数122.3。如果你仍然无法理解这个公式的作用,请在 Excel 中慢慢输入它,并注意自动弹出的公式提示。它会准确地告诉你每个部分的作用。

如果将上述列表中的所有项目放入各自的单独单元格中,则输出将是:

  1. 0
  2. 120
  3. 2
  4. 0.3

把它们放在一起,你会得到什么?Bibbidi Bobbidi Boo!把它们加起来就是你的答案0 + 120 + 2 + 0.3 = 122.3。2

为了进一步保持数据井然有序并处于您的控制之下,我建议复制所有新的单元格数据,并按照您的喜好添加秒数和小数,然后粘贴为仅限值进入新细胞。


  1. 如果 A1 已经格式化为文本,则保留它。Excel 仍会在单元格 B1 中的公式中正确使用它。警告,在格式之间来回转换可能会永久弄乱您的数据。我强烈建议使用数据的副本,而不是主文件,因为当您开始使用格式和文本到数字的转换(VALUE 公式的作用)时,很有可能您会意外地永久地将数据更改为不可用的内容。
  2. 此时你可能会问,为什么要将数字02和分开300.300是秒的部分,02也是秒。它们是一回事。好吧,你未来的数据可能会有一个特殊的分隔符,比如,;。我拥有的数据用它分开;,后面的数字实际上是该时间范围内的视频帧,所以我必须对其进行额外的计算才能获得十进制秒数。

答案3

回复:您收到 #VALUE! 错误的可能原因

RE: Value() 函数

示例(使用 xl 工作表输入并格式化单元格)

A1自定义格式为hh:mm:ss.000,然后输入16:48:30.001

B1一般格式就可以,输入公式:=TIMEVALUE(TEXT(A1,"hh:mm:ss.000"))

C1 输入公式:=TIMEVALUE(A1)

结果:

A1 16:48:30.000 B1 0.700347222 C1 #值!

细胞公式评论:

A1:16:48:30.000 只是输入的值,单元格格式为 hh:mm:ss.000

B1:=TIMEVALUE(TEXT(A1,"hh:mm:ss.000")) 这个函数的作用是将 A1 中的 VALUE 转换为 TIMEVALUE 函数所期望的“hh:mm:ss.000”格式的 TEXT

C1:=TIMEVALUE(A1) - 失败并显示 #VALUE! 因为 A1 已经是一个值,它只是被格式化为看起来像文本。

答案4

这对我有用:

=VALUE(3600*LEFT(A1,2))+VALUE(60*(MID(A1,4,2))+VALUE(MID(A1,7,2))+(VALUE(MID(A1,10,3))*0.001))

十分简单。

上一个答案中的毫秒从第 9 个字符开始,但实际上从第 10 个字符开始,因此最后一次计算的结果为“10”。我还需要将其乘以 .001 才能正确显示毫秒。

相关内容