add missing time interval in hourly data

I am looking for solution to add the missing time interval

Sample hourly data with missing hour

06.19.2011 00:00:00
06.19.2011 01:00:00
06.19.2011 02:00:00
06.19.2011 03:00:00
06.19.2011 04:00:00
06.19.2011 05:00:00
06.19.2011 07:00:00
06.19.2011 08:00:00

sample hourly data with two missing hours

06.19.2011 00:00:00
开发者_高级运维06.19.2011 01:00:00
06.19.2011 02:00:00
06.19.2011 05:00:00
06.19.2011 07:00:00
06.19.2011 08:00:00

So missing intervals need to be inserted to keep dynamic charts accurate

06.19.2011 03:00:00
06.19.2011 04:00:00

See if this works (assumes your data is in column A, starting in row 1):

Sub AddMissingTimes()
    Dim lastRow As Long, t as long

    lastRow = Range("A1").End(xlDown).Row

    For t = lastRow To 2 Step -1
        If DateDiff("h", Cells(t, 1), Cells(t - 1, 1)) <> -1 Then
            Cells(t, 1).EntireRow.Insert
            Cells(t, 1) = DateAdd("h", -1, Cells(t + 1, 1))
            t = t + 1
        End If
    Next t
End Sub

Does the data need to be date-specific and stored in the table? An SQL option that can be used in your source query would be to create a table with 24 records - one for each hour - then do a left join from the hours table to your table. For example:

Table zsysHours Field HourValue Data: 0, 1, 2...23

SELECT zsysHours.HourValue, HourlyData.Value FROM zsysHours 
LEFT JOIN HourlyData ON zsysHours.HourValue=HourlyData.HourValue

This can be done with date/time values as well, just makes it more complicated.