Need formula for determining number of consecutive dates.

Here are the two functions:

Public Function ConsecDays(DataCol As Range) As Integer
   Dim Row As Integer, Col As Integer
   Dim FirstRow As Integer, LastRow As Integer
   Dim CurDate As Date, PrevDate As Date
   Dim Count As Integer
   Dim Reset As Boolean
   
   Col = DataCol.Column
   FirstRow = DataCol.Row
   LastRow = FirstRow + DataCol.Rows.Count - 1
   PrevDate = ActiveSheet.Cells(FirstRow, Col)
   Count = 0
   Reset = True
   For Row = FirstRow + 1 To LastRow
      CurDate = ActiveSheet.Cells(Row, Col)
      If DateAdd("d", 1, PrevDate) <> CurDate Then
         Reset = True
      Else
         If Reset Then
            Reset = False
            Count = Count + 1
         End If
      End If
      PrevDate = CurDate
   Next Row
   
   ConsecDays = Count
End Function

Public Function LongestConsecDays(DataCol As Range) As Integer
   Dim Row As Integer, Col As Integer
   Dim FirstRow As Integer, LastRow As Integer
   Dim CurDate As Date, PrevDate As Date
   Dim Count As Integer, MaxCount As Integer
   
   Col = DataCol.Column
   FirstRow = DataCol.Row
   LastRow = FirstRow + DataCol.Rows.Count - 1
   PrevDate = ActiveSheet.Cells(FirstRow, Col)
   Count = 1
   MaxCount = 0
   For Row = FirstRow + 1 To LastRow
      CurDate = ActiveSheet.Cells(Row, Col)
      If DateAdd("d", 1, PrevDate) = CurDate Then
         Count = Count + 1
      Else
         If Count > MaxCount Then
            MaxCount = Count
         End If
         Count = 1
      End If
      PrevDate = CurDate
   Next Row
   
   LongestConsecDays = MaxCount
End Function

Select allOpen in new window

To use it: =ConsecDays(A5:A25) and =LongestConsecDays(A5:A26)
Note: Consecutive Days is 4 not 3.

Oh boy -- not sure what all this is, or how to use it. I should have been more specific and asked if this could be done in an Excel formula. Not sure how to use your answer (but I'm sure it works!)

As for the longest current Consecutive Days, it really is 3 (06/27, 06/28, 06/29.)  The longest consecutive days is 8. Thanks!

There is 4 : (1/23 - 1/24), (6/11-6/12-6/13), (6/18-6/25), and (6/27-6/29).

Those above are VBA codes. I don't think there is any formula or combination of it in Excel that you can calculate those. Only choice is use VBA. You just insert above into a VBA module; and afterwards, you have 2 formulas that can calculate the way you want it.

No Comments added further and OP wanted formula based solution.

Thanks, Shums. Perfect answer. Unfortunately I had to travel out of town right after I asked this question. Thanks again!