Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Comparing time values which have milliseconds in them e.g 10:20:30

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
--
Jon Stickings
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,624
Default Comparing time values which have milliseconds in them e.g 10:20:30

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.

In article ,
Jon Stickings wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Comparing time values which have milliseconds in them e.g 10:20:30

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.

"Jon Stickings" wrote:

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
--
Jon Stickings

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Comparing time values which have milliseconds in them e.g 10:2

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
--
Jon Stickings


"JE McGimpsey" wrote:

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.

In article ,
Jon Stickings wrote:

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


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,624
Default Comparing time values which have milliseconds in them e.g 10:2

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.

In article ,
Jon Stickings wrote:

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."



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Comparing time values which have milliseconds in them e.g 10:2

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
--
Jon Stickings


"Benjo" wrote:

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.

"Jon Stickings" wrote:

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
--
Jon Stickings

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to add a column of time values eg 72hours plus 34hours 52mins David R Excel Discussion (Misc queries) 1 August 29th 06 10:26 PM
comparing two columns of data to find common values patman Excel Discussion (Misc queries) 2 July 25th 06 03:05 PM
Comparing values between columns only when there are values in bot Mark K Excel Worksheet Functions 1 February 19th 06 07:47 PM
Fun with Time values wisperc Excel Discussion (Misc queries) 1 January 5th 06 03:51 AM
Calculating Time Values in Excel STELFOXJ Excel Worksheet Functions 0 September 13th 05 02:33 PM


All times are GMT +1. The time now is 06:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"