Thread: Converting from GMT -> EST and Date & Time in HH.MM.DD.SS.MS format

Hello all:

I'm new to the board, but can see this being a valuable resource for me in the future. I am trying to do two things:

1. Convert date and time in this format from GMT -> EST for these dates / times.

Current format is: YYYY-MM-DD-HH-MM-SS-MSMSMSMSMSMS
2010-03-18-03.55.30.477460
2010-03-18-03.55.30.648572

2. After converting, I need to find out the difference between the two dates / times.

Would appreciate any help anyone can provide.

Thank you in advance!

Does this work for you in your own locale ?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-"," ",3),".",":"),":",".",3)-"05:00"
format cell as yyyy-mm-dd hh:mm:ss.000

For the difference between the various times simply subtract results generated by above from one another.

NOTE: XL will only display to .###s so you might lose some of your ms

The formula I provided should based on your sample and my suggested format return:

2010-03-17 22:55:30.477
2010-03-17 22:55:30.649

Dates are Integers / Time is Decimal (ie noon = 0.5) ... on that basis you can establish differences using the same principles as other numerical calculations, ie if the above values reside in B1 & B2 respectively then:

B3: =B2-B1
format as [hh]:mm:ss.000

would return 00:00:00.0172

If the above is not working for you please post a sample file.

As previously outlined - the difference is simply End - Start, ie formula for F2 being:
=E2-D2
format cell as [hh]:mm:ss.000 (or however you wish)