Get missing records from the Date series

[ThrottleWorks]

Hi,

Please see attached file for reference.
Column A is Date records. Column F is the result.
Dates in column A might be duplicate too. For example 29-07-2021 can be present 3 times in the data.

We need to check last date from the records (usually last row only).
In our example, last date from the record is 29-07-2021.
Once we get the last date then we will deduct 30 days from this date, this will be our from date.
In our example, from date will be 29-06-2021.

So our From Date to To Date range will be 29-06-2021 to 29-07-2021.
Now we need to find missing dates in column A from this range.
Result is in F column. These dates are missing in column A for the range.

How can I do it, what should I use to get the missing details. Can anyone please help me in this.
Take care.

 

[Marc L]

Hi, according to your attachment a VBA demonstration for starters where column C is not necessary :​

Sub Demo1()
        Dim V, W, X, D%
    With [F1].CurrentRegion.Rows
        If .Count > 1 Then .Item("2:" & .Count).Clear
    End With
        V = [A1].CurrentRegion.Value2
        W = Evaluate("TRANSPOSE(ROW(" & V(UBound(V), 1) - 30 & ":" & V(UBound(V), 1) & "))")
        X = Application.Match(W, V, 0)
    For D = 1 To 31
        If IsNumeric(X(D)) Then W(D) = False
    Next
        W = Filter(W, False, False)
    If UBound(W) > -1 Then
            W = Application.Transpose(W)
            ReDim Preserve W(1 To UBound(W), 1 To 2)
            For D = 1 To UBound(W):  W(D, 2) = V(UBound(V), 1) - W(D, 1):  Next
        With [F2:G2].Resize(UBound(W))
            .Columns(1).NumberFormat = "m/d/yyyy"
            .Value2 = W
        End With
    End If
End Sub

 

[ThrottleWorks]

Hi @Marc L sir, thanks a lot for the help. I am checking this.
Have a nice day ahead. Take care.

 

[ThrottleWorks]

Hi @Marc L sir, it is working perfect, thanks a lot. Sorry for delay in reply.
Have a nice day ahead.