Finding missing dates

[sassiisarah]

Hi,

I have a very large date sequence in which dates repeat themselves multiple times (e.g 5/31, 5/31, 5/31, 6/1, 6/1, 6/2, etc). But some dates are missing in this type of date list and I am not sure how to find the missing dates, as I have about 400,000 rows of these dates in this format. Can someone please help me find a way to do this? Thank you!!

 

[MikeDBMan]

This should work. Note the 2 caveats within the code.

Sub MissingDate()
Dim X, Y, Fnd, cnt as long
Dim MissingDates(100000, 1) As Date
Dim TheDate As Date
'Place cursor at first date in list before executing this macro.  Dates MUST BE sorted before executing this macro.
Let X = ActiveCell.Row
Let Y = ActiveCell.Column
Let TheDate = Cells(X, Y).Value
Do While True
    X = X + 1
    If Cells(X, Y).Value = TheDate Then
        'date is same as previous do nothing
    ElseIf Cells(X, Y).Value = Empty Then
        'last date, exit while
        Exit Do
    Else
        'Different date
        Let cnt = 0
        Do While True
            Let cnt = cnt + 1
            If TheDate + cnt = Cells(X, Y).Value Then
                Let TheDate = Cells(X, Y).Value
                Exit Do
            Else
                Fnd = Fnd + 1
                Let MissingDates(Fnd, 1) = TheDate + cnt
            End If
        Loop
    End If
Loop

Workbooks.Add Template:="Workbook"
MyTemp = ActiveWorkbook.Name
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Missing Dates"
Cells(1, 1).Value = "Missing Dates"
For X = 1 To Fnd
    Cells(X + 1, 1).Value = MissingDates(X, 1)
Next
Columns("A:A").EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
End Sub