Time and date difference in 2 cells in Excel

Question

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 ...

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

Answer 1

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

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.

For the difference from B27 to B31,

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

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

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.

Answer 2

If the cells propreties are correct, Excel should be able do compute a difference between them without any problem.

Both of the cells containing the dates must be set with Date/Hour format, the cell containing the result of the difference can be (for instance) set to Standard. Then the difference will be a number (integer or float). For instance :

• If the result is 3, it means 3 days, multiply it by 24 to have the number of hours.
• If the result is 3,6667, the integer part gives you the number of full days, the float part gives you the number of hours. 0.6667*24 = 16 hours.

Hope it helped