Difference between timestamps in excel

1
votes
Start Time                              End Time

24-OCT-2019 01.36.38.219226000 PM       24-OCT-2019 06.52.27.102652000 PM

I have timestamps in format which has milliseconds part in it. I need difference between start time and end time. (Preferably in format hh:mm:ss ). I googled and what I found is TEXT(B1-A1,'hh:mm:ss'). However this is not working with time format 24-OCT-2019 01.36.38.219226000 PM. Please suggest an alternative.

1
excelexcel-formula
Maximum you will reach in Excel is miliseconds if you choose the format: dd-mm-aaaa hh:mm:ss,000 AM/PM;@. To reach to your final result, I recommend you to work until seconds as first target and with the remaining as second target. With your actual format, you can collect the second fractions with InStr(1,Right(Cell,13),.,10) – David García Bodego

1 Answers

3
votes

Divya Sam, considering that the info is in columns A and B, put this formula in column C:

=TEXT(VALUE(SUBSTITUTE(LEFT(B2,20)&RIGHT(B2,3),".",":"))-VALUE(SUBSTITUTE(LEFT(A2,20)&RIGHT(A2,3),".",":")),"hh:mm:ss")