Excel中2个单元格中的时间和日期差异 [英] Time and date difference in 2 cells in Excel

问题描述

我在B列的随机行上有带有日期时间戳的Excel，例如:单元格之间的位置为空( B2 ， B3 .. etc).

I am having Excel with Date time stamp at random row in B Column, Ex: Where in between cell are Empty ( B2, B3..etc).

B1 :  15:13:48:335 2014/08/06
B27:  15:13:55:955 2014/08/06
B31:  15:14:16:005 2014/08/06 ...

我需要找到两个连续条目之间的时间差，例如: B21-B1 和 B31-B27 等.

I need to find the time difference between 2 consecutive entries Ex: B21-B1 and B31-B27 and so on.

推荐答案

如果显示的值是实际的日期时间，则它们的数字似乎随着行的增加而逐渐增大.

If the values you've shown are actual datetimes, then they are numbers that seem to grow progressively larger as the rows increase.

要获取B1与B27之间的差异，

To get the difference from B1 to B27,

=LARGE(B:B, 2)-LARGE(B:B, 3)

以您喜欢的任何方式将结果设置为时间格式.

Format the result as time in any way you prefer.

对于B27与B31之间的差异，

For the difference from B27 to B31,

=LARGE(B:B, 1)-LARGE(B:B, 2)

当日期时间是实际日期时间而不是文本时， LARGE函数可以与其他任何数字一样使用.

When datetimes are actual datetimes and not text, the LARGE function can be used just like any other number.

如果B列中的值是文本，请先将它们还原为正确的日期时间.使用类似以下的内容，

If your values in column B are text, start by reverting them to proper datetimes. Use something like the following,

=DATEVALUE(RIGHT(B1, 5)&"/"&MID(B1, 14, 4))+TIMEVALUE(LEFT(B1, 8)&"."&MID(B1, 10,3))

首先更正您的数据；然后担心操纵数字.

Correct your data first; then worry about manipulating the numbers.

这篇关于Excel中2个单元格中的时间和日期差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！