Difference of Datetimes in MS Excel 2007

Question

In Excel 2007, I want to do date difference for the following dates as below:

  A                         B                          C
1 Date1                     Date2                      Difference of A and B
2 2009.11.28 01:25:46:0287  2009.11.28 01:25:46:0287   ?
3 2009.11.28 01:25:46:0443  2009.11.28 01:25:46:0443   ?

Kindly help me with a formula in Excel 2007, to get the difference of the above dates (?).

Answer 1

Excel stores Dates as a number equal = number of days since 01/01/1900, hence you can just take the difference. i.e.

C2 = A2-B2

Will give you difference in days.

If you want it in seconds, for example, then:

C2 = (A2-B2) * 24 * 60 * 60

If you need to parse the string including the fractions of seconds then:

=DATE(MID(A2,1,4),MID(A2,6,2),MID(A2,9,2))+TIME(MID(A2,12,2),MID(A2,15,2),MID(A2,18,2))+MID(A2,21,4)/(24*60*60*10000)

Will convert A2 into an Excel DateTime. I suggest you put it in cell d2 and copy to e2. C2 can then be based off those.