Create time series in milliseconds

Question

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?

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

Unknown Unknown · Accepted Answer · 2015-09-26T23:52:08

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.

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.

Create time series in milliseconds

2 Answers