我遇到的问题是,我留下了一个这样的时间戳: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
视为文本字符串,然后将其部分转换为数字,然后将它们适当地加在一起,使其成为总秒数。它有四个部分:
VALUE(3600*LEFT(A1,2))
VALUE(60*(MID(A1,4,2))
VALUE(MID(A1,7,2))
VALUE(MID(A1,9,3))
查看字符串00:02:02.300
,您会发现它基本上是由:
和分隔的四个数字.
。这四个数字中的每一个都对应于上面的四个部分,从左到右。
VALUE(3600*LEFT(A1,2))
是将00
小时位置的 转换为秒。第二项处理下一个数字02
,将其转换为分钟。依此类推。现在,请注意,在完整公式中,上面列表中的每个项目都是添加 +
合在一起。这样你就得到了秒数和小数122.3
。如果你仍然无法理解这个公式的作用,请在 Excel 中慢慢输入它,并注意自动弹出的公式提示。它会准确地告诉你每个部分的作用。
如果将上述列表中的所有项目放入各自的单独单元格中,则输出将是:
0
120
2
0.3
把它们放在一起,你会得到什么?Bibbidi Bobbidi Boo!把它们加起来就是你的答案0 + 120 + 2 + 0.3 = 122.3
。2
为了进一步保持数据井然有序并处于您的控制之下,我建议复制所有新的单元格数据,并按照您的喜好添加秒数和小数,然后粘贴为仅限值进入新细胞。
- 如果 A1 已经格式化为文本,则保留它。Excel 仍会在单元格 B1 中的公式中正确使用它。警告,在格式之间来回转换可能会永久弄乱您的数据。我强烈建议使用数据的副本,而不是主文件,因为当您开始使用格式和文本到数字的转换(VALUE 公式的作用)时,很有可能您会意外地永久地将数据更改为不可用的内容。
- 此时你可能会问,为什么要将数字
02
和分开300
?.300
是秒的部分,02
也是秒。它们是一回事。好吧,你未来的数据可能会有一个特殊的分隔符,比如,
或;
。我拥有的数据用它分开;
,后面的数字实际上是该时间范围内的视频帧,所以我必须对其进行额外的计算才能获得十进制秒数。
答案3
回复:您收到 #VALUE! 错误的可能原因
- TimeValue() 需要一个 TEXT 值,并且很可能引用了时间值。请参阅https://support.office.com/en-us/article/how-to-correct-a-value-error-in-the-timevalue-function-d2a5e995-3586-4611-93a7-acfc7766dfff 或谷歌搜索“如何纠正 TIMEVALUE 函数中的 #VALUE! 错误”。
RE: Value() 函数
- Excel 中不需要此功能,它是为了与其他电子表格程序兼容而提供的。请参阅https://support.office.com/en-us/article/how-to-correct-a-value-error-in-the-timevalue-function-d2a5e995-3586-4611-93a7-acfc7766dfff 或谷歌搜索“Excel 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 才能正确显示毫秒。