Creating Data for Missing Time Series

[belznp1]

I have data at one second intervals for a set of drivers. Occasionally, this data will skip a second and I need to create a new row and interpolate between the values this missing second is bounded by. There are also instances where there are several skipped seconds in one file. I am having a bit of trouble with the code. Any help?

The data looks something like this:

44.35545 -73.2485 4.8 12 57 31
44.35547 -73.2485 2.5 12 57 32
44.35548 -73.2485 1.9 12 57 33
44.35547 -73.2485 1.5 12 57 34
44.35548 -73.2486 1.4 12 57 35
44.35547 -73.2485 1.7 12 57 37
44.35547 -73.2485 2.0 12 57 38
44.35548 -73.2485 3.7 12 57 39
44.35550 -73.2486 4.5 12 57 40

where the columns represent Latitude, Longitude, Speed, Hours, Minutes, Seconds.

[belznp1]

also, this is what I have so far - which only inserts the missing row and the second value:

Sub InsertMissingTime()
'
' InsertMissingTime Macro
' Identifies missing time series and inserts interpolated data
'
'
' Keyboard Shortcut: Ctrl+m
'
Dim lngRow As Long
Dim intCol As Integer
Dim lngRowCount As Long
Dim intIns As Integer
Dim m As Integer
Dim n As Long

'dimension length of timeseries
i = Range("F:F").Cells.SpecialCells(xlCellTypeLastCell).Row

'select row with second timestamp
Range("O1:O" & i).Select

'find start row
lngRow = Selection.Row
intCol = Selection.Column

'find number of rows
lngRowCount = Selection.Rows.Count

For n = lngRow To lngRow + lngRowCount - 1
'calculate difference in timestamps
intIns = Cells(n + 1, intCol).Value - Cells(n, intCol).Value
If intIns > 1 Then
'insert row(s) and include missing time
For m = 2 To intIns
Cells(n + m - 1, intCol).EntireRow.Insert Shift:=xlShiftDown
Cells(n + m - 1, intCol).Value = Cells(n, intCol).Value + m - 1
lngRowCount = lngRowCount + 1
Next m
End If
Next n
Exit Sub
End Sub

[SamT]

PseudoCode:


Start Loop
With Seconds_Column
If ThisRow.Value = NextRow.Value - 1 Then
ThisRow = ThisRow + 1
Loop
Else
NumRowsToInsert = (NextRow.Value - ThisRow.Value) - 1
End With

With Data Columns
LatDiff = (NextRow,LatColumn.Value - ThisRow,LatColumn.Value) / NumRowsToInsert
Etc
End With

Insert NumRowsToInsert After ThisRow

For i = 1 to NumRowsToInsert
(ThisRow + i),LatCol.Value = ThisRow,LatCol.Value + (LatDif * i)
Repeat for all data columns including seconds
Next i

ThisRow = NextRow + NumRowsToInsert
End Loop

[mdmackillop]

This will insert missing seconds.

Sub InsertTimes()

Dim c As Range
Dim x As Long, i As Long
Dim sec As Double
x = Cells(Rows.Count, 4).End(xlUp).Row
sec = TimeSerial(0, 0, 1) - TimeSerial(0, 0, 0)
For i = x To 2 Step -1
Set c = Cells(i, 4)
t2 = TimeSerial(c, c.Offset(, 1), c.Offset(, 2))
t1 = TimeSerial(c.Offset(-1), c.Offset(-1, 1), c.Offset(-1, 2))
If t2 - t1 > sec Then
c.EntireRow.Insert
t = t2 - sec
c.Offset(-1) = Hour(t)
c.Offset(-1, 1) = Minute(t)
c.Offset(-1, 2) = Second(t)
i = i + 1
End If
Next
End Sub