3

I have a device that records time in this format: hh:mm:ss:ms, recording one data point every 40 ms. In other words, I have a time column that increases by 40 ms per cell: 

15:07:57:000   
15:07:57:040  
15:07:57:080  
15:07:57:120  
15:07:57:160  
15:07:57:200  
...

I have 25 minutes of recordings for more than 50 subjects collected at different times of the day. In order to analyze the data, I need to create a new Excel file with Participant code, Time from 0 to minute 25, outcome variable etc.

The time column should be: mm:ss:ms 00:00:000 to time: 25:00:000

I tried to create a new column with custom format cell: hh:mm:ss.000;@ But, if I tried to autofill, it just keeps adding 40 to milliseconds ad infinitum, not counting seconds and minutes.

How can I create a time variable that just counts minutes, seconds and milliseconds with an increase of 40 ms per cell?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Glu
  • 327
  • 3
  • 14
  • 1
    http://stackoverflow.com/a/3095530/293078 – Doug Glancy Sep 26 '15 at 23:38
  • Update: I tried this: Cell B1: cell Format = hh:mm:ss.000;@ I tiped in the cell; 00:00:00:000 I copied in cell B9, then, i typed in cell B9: = =B1+ORARIO(0,0,0.4) (orario means Hour...I have excel in italian)... But it gives me an error called "Value"...I assume because Seconds must be between 0 and 59 and milliseconds are not allowed... I just saw your answers...I'll take a look and let you know...(Thank you) – Glu Sep 26 '15 at 23:45

2 Answers2

2

You are going to want to create all time values based on an adjustment of the original time (e.g. 00:00:00.000). Time often does not compute to finite values; usually ending in a repeating decimal or truncated decimal where rounding off will magnify the error if created on the last new value. In engineering, this is known as datum dimensioning where all dimensions are taken off of a baseline rather than incremental dimensioning where the potential error stacks up.

        Time Increments

After formatting A2 as hh:mm:ss.000, put the following into A2,

=TIME(0, 0, 1)/25*(ROW(1:1)-1)

Fill down to A37502 to complete a full 40ms increment pattern for 25 minutes.

Note that the TIME function does not support creating a decimal of a second itself. One second (e.g. 00:00:01) equals 0.0000115740740740741 to a 15 digit precision. This is arrived at with Time(0, 0, 1). 40ms is ¹⁄₂₅ of a second and that 40ms increment must be multiplied for each successive row.

  • OMG, Jeeped, thank you SO MUCH ! You just saved me so much time. That worked perfectly !! Also, thank you for explaining the problem. If you were anywhere close I would hug you and at least offer you a drink =) And thank you anybody who even tried =) – Glu Sep 27 '15 at 00:05
1

There is a lot of calculation involved in a formula that derives each value independently 37,500 times.

In one cell: 

15:07:57.000

and in the cell immediately below (or immediately to the right): 

15:07:57.040

with both selected and copied down (or across) the inaccuracy after 25 minutes worth is probably less than 50 millionths of a millisecond so will not be visible with display to one millisecond precision. When comparing times it is in any case often advisable to round appropriately. And series fill requires no formulae, so is fast to create and does not slow calculation times.

With two cells selected (here A2 and A3, with A2 starting at 0 and A2 40 ms later), when the cursor is hovered over the fill-handle (a small square at bottom right) it changes shape to a cross (exaggerated in the example!):

enter image description here

Left-click in that state, keep depressed and drag down to suit. For so many rows it may be quicker to select A2 and use HOME > Editing - Fill, Fill Series, Series..., Series in Columns, Type Linear, Step value: 0.000000462962962962963, Stop value: 0.0173611111111111 but requires care in entering the numbers and, I suspect, may at some points be a little more inaccurate (in millionths of a millisecond) than dragging.

Starting in B1 with 1, stepping 1 and stopping at '2000000' fills over 1 million rows in the blink of an eye.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • pnuts, I tried series fill, but I could not get it to work appropriately. I would really be interested in knowing how would you do that to get a result like the one displayed in the picture posted by jeeped. Thank you very much. – Glu Sep 27 '15 at 19:21
  • 1
    I tried your solution. I can't believe that I couldn't make it work earlier, I was sure that I tried this already. Anyway, it is a little strange because it looks like it rounds the time...but it still works quite fine for what I need. Thank you – Glu Sep 27 '15 at 19:40
  • 2
    Ok, final comment (I think): I've got EXACTELY what I wanted by doing this: first cell: format -> custom format -> mm:ss.000;@ Value in the first cell: 00:00.00 Second Cell: 00:00.040 Select first and second, and copy down. Both solutions worked great. It is true that the solution proposed by Jeeped takes a lot of computation. I selected the all column and tried to copy to another column and excel crushed. But, anyway, both solutions are great. Again, thank you everybody – Glu Sep 27 '15 at 19:43
  • 2
    You are right, this solution worked perfectly and in the blink of an eye. Thank you so much. I knew how to drag down, but at the beginning when I was trying I had the problem that it kept adding 0.40 to milliseconds over and over, without considering seconds and minutes. Anyway, thank you so much. I learned new things. Just out of curiosity, can I ask you how did you know that to stop at row 37503, the values that you have to use are: Step value: 0.000000462962962962963, Stop value: 0.0173611111111111 ?? – Glu Sep 27 '15 at 20:23