Consecutive Weeks

[errollflynn]

Hello,

I have a payroll report that has a list of pay periods and associated pay dates where a single employee will have multiple rows for multiple dates. I am trying to find a way to identify instances of consecutive dates with the goal being to find instances where an employee has received payment over the course of 10 consecutive pay periods.

Thank you.

 

[sijpie]

Here is a UDF (userdefinedfunction) to do the counting for you. Put it in a VBA module and save your workbook as a .xlsm type.

Function ConsecutivePayDays(sKey As String, rPayTable As Range, rKey As Range, rPayPeriod As Range, Optional bDescendingDates As Boolean = True) As Long
    Dim vIn As Variant
    Dim lR As Long, lCkey As Long, lCpayp As Long, lCount As Long, lUB1 As Long, lPayInt As Long
    Dim dtLast As Date
    Const lPAYINTERVAL As Long = 7      '<<<<<<< payment interval = each 7 days
    
    If Intersect(rPayTable, rKey) Is Nothing Or Intersect(rPayTable, rPayPeriod) Is Nothing Then
        ConsecutivePayDays = -1
        Exit Function
    End If
    
    vIn = rPayTable.Value
    lUB1 = UBound(vIn, 1)
    
    lCkey = rKey.Column - rPayTable.Column + 1
    lCpayp = rPayPeriod.Column - rPayTable.Column + 1
    
    lPayInt = IIf(bDescendingDates, -lPAYINTERVAL, lPAYINTERVAL)
    For lR = 1 To lUB1
        If vIn(lR, lCkey) Like sKey Then
            Select Case True
                Case lCount = 0
                    'first encounter of Key
                    lCount = 1
                    dtLast = vIn(lR, lCpayp)
                Case vIn(lR, lCpayp) = dtLast + lPayInt
                    'date is consecutive with previous
                    lCount = lCount + 1
                    dtLast = vIn(lR, lCpayp)
                Case vIn(lR, lCpayp) = vIn(lR - 1, lCpayp)
                    'Same paydate as line above - do nothing
                Case Abs(vIn(lR, lCpayp) - vIn(lR - 1, lCpayp)) > lPAYINTERVAL
                    'not consecutive - stop counting
                    Exit For
            End Select
        End If
    Next lR
    ConsecutivePayDays = lCount
End Function

To use it start typing
=cons
and you will see the ConsecutivePayDays() function in the proposed list. double click it.
There are 5 parameters. The 5th you can leave out if your table is sorted with descending dates

=ConsecutivePayDays(Keyname or ID to look for, DataTable, Column in table with the Key or ID, Column in table with PayPeriod, Descending (true= default) or ascending( false))

 

[sijpie]

jun23.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Key	LN	FN	FullN	SSN	Hrs	PPs	Ppe	LPD
2	lname1						24/04/2022			lname2	11
3	lname1						17/04/2022			lname1	5
4	lname1						10/04/2022
5	lname1						03/04/2022
6	lname1						27/03/2022
7	lname2						13/11/2022
8	lname2						06/11/2022
9	lname2						30/10/2022
10	lname2						23/10/2022
11	lname2						23/10/2022
12	lname2						16/10/2022
13	lname2						09/10/2022
14	lname2						02/10/2022
15	lname2						25/09/2022
16	lname2						18/09/2022
17	lname2						11/09/2022
18	lname2						11/09/2022
19	lname2						04/09/2022
20	lname2						20/09/2020
21	lname2						13/09/2020
22	lname2						06/09/2020
23	lname2						30/08/2020
Sheet6

Cell Formulas
Range	Formula
K2	K2	=ConsecutivePayDays(J2,A1:I23,A1:A23,G1:G23)
K3	K3	=ConsecutivePayDays(J3,A1:I23,A1:A23,G1:G23)
G3:G6,G8:G10,G12:G17,G19,G21:G23	G3	=G2-7
G11,G18	G11	=G10
G20	G20	=G19-714