How to count the number of gaps in a sequence?

Question

Hello everyone,

How would I go about counting the number of gaps in a dated sequence of invoices that reset at 500 every day?  For example

Column A   Column B

Date:             Invoice #

1/1/2018       500

1/1/2018       501

1/1/2018       502

1/1/2018       504

1/1/2018       507

1/1/2018       508

1/1/2018       509

1/1/2018       510

1/2/2018       500

1/2/2018       501

1/2/2018       503

1/2/2018       504

1/2/2018       506

Column A continues day after day with the invoice number count resetting to 500 every day.  I need to know how many invoice gaps there are everyday.  Not what numbers are missing, just how many.   For example on 1/1/2018 there are approximately 3 missing invoices and 2 missing invoices on 1/2/2018.  Thank you!

Answer 1

Newer version setting first invoice expected to 500

best wishes

Function missing(mydate)

   Application.Volatile

   lastrow = Cells(Rows.Count, "A").End(xlUp).Row

   olddata = 499

   mymiss = 0

   For k = 2 To lastrow

     If Cells(k, "A") > mydate Then Exit For

     If Cells(k, "A") = mydate Then

       mycount = mycount + 1

       newdata = Cells(k, "B")

       If newdata > olddata + 1 Then

         mymiss = mymiss + (newdata - olddata) - 1

       End If

       olddata = newdata

    End If

    Next k

    missing = mymiss

End Function