Difference of Datetimes - MS Excel 2007

Question

In Excel 2007, I want date difference for the following string dates as below: the below data are in strings. Mean to say that i want difference by converting string to date time and then get the result as difference of two converted datetimes.

  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

I couldn't come up with a really nice way of doing this... hopefully someone else will. Having said that, the following may give you what you need.

To convert the main date part, use the following formula (this assumes the string date is in A1):

=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2)) +
    TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))

To convert the fractional second part, use:

=VALUE(MID(A1,21,4))/10000

The date / time part can easily be subtracted, as can the fractional second part.

The place I ran into trouble was recombining those parts into a whole that Excel will actually display in a sensible way. I finally took the difference between the two dates and multiplied by 86400 (= 24 * 60 * 60 - number of seconds in a typical day) then added the difference in the fractional second part.

Hope this helps. Regards, Richard

P.S. There are quite a lot of things I don't like about this solution, the biggest of which is the fragility of the formulas - if the date format changes, the formulas will need to be adjusted.