Differences between string representations of date/times

In Excel 2007, I want the differences for the following string date/times:

开发者_高级运维

  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   ?

I want the differences by converting strings to date/time and then the results as differences of two converted date/times.

---

A rather long-winded way to calculate zero (for the examples):

=SUBSTITUTE(LEFT(A2,10),".","/")+MID(A2,12,8)+RIGHT(A2,4)/86400000-(SUBSTITUTE(LEFT(B2,10),".","/")+MID(B2,12,8)+RIGHT(B2,4)/86400000)  

By special request and very slightly shorter:

=SUBSTITUTE(LEFT(A2,10),".","/")+REPLACE(RIGHT(A2,13),9,1,".")-(SUBSTITUTE(LEFT(B2,10),".","/")+REPLACE(RIGHT(B2,13),9,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.