Comparing time values which have milliseconds in them e.g 10:20:30

[Guest]

Hi All,

As part of a performance test that I am going to be running, I am planning
to extract some time values from the application under test log files, paste
the values into Excel and calculate the time taken for certain transactions.
I have been looking into this and have become stuck because the time values
in the log are in the format <hh:mm:ss:msmsms> where ms is for milliseconds.
My plan was to substract one value from the other, thus getting the
difference which would be the time taken for the transaction e.g. (cell
A1)10:20:30.400 (cell B1)10:20:40.500 Formula in cell C1 would be =B1-A1.
This is expected to return the value of 00:00:10.100. However, all that is
returned is #VALUE!. The formula works fine without the milliseconds, but as
I am doing a performance test, I need to use milliseconds.

I have messed about with the format of the cell/time, but have not been
successful.

Any help in this matter is very much appreciated.

Regards, Jon

 

[JE McGimpsey]

If you're getting #VALUE! it's probably because your values are entered
as Text rather than numbers, since

A1: 10:20:30.400
B1: 10:30:30.500
C1: =B1-A1 ==> 0:00:00.100

when C1 is formatted as h:mm:ss.000

Are your times entered as hh:mm:ss.000? Or are they hh:mm:ss:0000 (which
would be interpreted as Text by XL's parser)?

"messing around" with the format won't affect anything, since math
operations occur with the underlying values, regardless of the display
format, and Text entries are not affected by changing the displayed
number format.

 

[Guest]

I believe number formats don't affect the numbers or the interpretations
thereof, just the display.

If you could read in (open) the text data file and have excel treat the
colon as a separator, you'd have the hours, minutes and seconds in diferent
columns. Then calculate the time value, where excel uses the number's
integer portion to be the day, and the decimal portion to be the time of day.
(ie. .25=6am, .5=Noon, .75=6p, etc.)

Therefore hh/24 + mm/(24*60) + ss.ms/(24*60*60) = time of day.
--Just a lurker.

 

[Guest]

Hi JE,

Thanks for the quick response.

Below is a copy of one of the lines from the log. This is one string which
I paste in, then chop up using the 'Text to Columns' feature. This allows me
to remove unwanted data and also (if it worked) perform the comparison. I
think that the main issue is that the milliseconds are recorded in the log
file as .000 as opposed to :000. I think that the full stop instead of a
colon is the stumbling block.

"21/09/2006 16:22:17.918 W2118R02 ftgt156 Minton.exe
0x00000d98 0x0d9c L CMinton::OnCardRemoved() - Started."


Again any help is appreciated

Regards, Jon

 

[JE McGimpsey]

No, the full stop is what you need (assuming you use that as a decimal
separator). When I use Data/Text to Columns on your example string, and
format the appropriate cell as hh:mm:ss.000 it works perfectly:

16:22:17.918

Using a colon will absolutely make XL treat the string as Text.

 

[Guest]

Hi Benjo,

Thanks for the reply. Going to go with JE's reply as this works and is
easy, but thanks for taking the time to answer anyway.

Cheers, Jon