# consecutive dates

#### gurutrue

##### New Member
I have a table with two fields EmployeeID and AbsenceDate. I have another table that has one field Holidays.

What would be the best way to determine the number of consecutive dates that were taken?

Something like:

EmployeeID ConsecutiveAbsences
HJ745278 2
HF969699 1

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I found some VBA code which I have modified to suit my purpose. It functions mostly correctly and outputs data into the Immediate window. Now I am not a VBA pro by any means. Can anyone point me in the right direction to get the data to output into a table within Access or output to a text file.

Public Function Consecutive()
Dim db As Database
Dim rs As Recordset
Dim n As Integer
Dim AStartDate As Date
Dim sql As String
Dim MyText As String
Dim MyNum As Integer

sql = "SELECT EmployeeID, AbsenceDate " & _
"FROM Employee_Absences_UNION " & _
"ORDER BY EmployeeID, AbsenceDate;"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
rs.MoveFirst
Do Until rs.EOF = True
n = 0
AStartDate = rs.Fields("AbsenceDate")
Do Until rs.EOF = True
If rs.Fields("AbsenceDate") = DateAdd("d", n, AStartDate) Then
n = n + 1
rs.MoveNext
Else
Exit Do
End If
Loop

rs.MovePrevious
Debug.Print rs.Fields("EmployeeID") & "," & AStartDate & "," & rs.Fields("AbsenceDate")
rs.MoveNext
Loop
Set db = Nothing
Set rs = Nothing
End Function

Replies
1
Views
269
Replies
9
Views
219
Replies
3
Views
223
Replies
8
Views
159
Replies
1
Views
225

1,203,051
Messages
6,053,221
Members
444,648
Latest member
sinkuan85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back