Calculation using time to hundreths of a second

[charlottetat]

Hi All.

I have data in .csv format. The data has a start time on one row in HH:MM:SS.00 format and an end time on the row below in the same format. I import the data into Excel and have a start time in F2 and an end time in F3.

I am trying to find out the time difference between F2 and F3. I've tried formatting the column with the times and the column with the calculation with a custom time of HH:MM:SS.00 then doing calculation =F2-F3 but the cell just shows #VALUE!.

Any replies are greatly appreciated

 

[barry houdini]

That implies that the "time values" are text-formatted and can't be "co-erced" to time values by your calculation. There may be other characters in there, what do you get with

=LEN(F2)

If that's not 11 you might have extra characters in there

 

[charlottetat]

Thanks Barry - LEN(F2) returns 13 - and upon further inspection the time has a space at the start and the end. DOH!

 

[barry houdini]

It might be some other character other than a space, although it might look like a space. If there's always an extra character at the start then this should work to get the time difference

=MOD(F3,2,11)-MOD(F2,2,11)