VBA function to return date of first direct deposit date in particular month

[Euler]

I'm working on automating some processes for a company using MS Access to control Excel. One of the reports depends on when direct deposits happen in a month. A month can have two or three two-week pay periods depending on when the direct deposit dates fall. For example, in 12/22 there were two direct deposit dates of 12/14 and 12/28. (The pay period ending dates are always exactly seven days prior to the direct deposit date.) In 11/22, there were three direct deposit days of 11/2, 11/16 and 11/30. This made one of the November pay period ending date fall in October since it was a week before 11/2.



What I'm trying to do is create a function that will return the first direct deposit date in any particular month of any year. I know the last direct deposit date of 2022 was 12/28 so I think the function will need to continue to add 14 days to 12/28/22 until it finds the sought after month in the sought after year. This is what I have so far:

Private Function FindFirstDDinMonth() As Date
    Dim i As Integer
    Dim Target Month As Integer
    Const IncrementDays As Integer = 14
    Target Month = Month(DateAdd("m", -1, ReportCreationDate))
    For i = 1 To 12
        If Month(DateAdd("d", i * IncrementDays, #12/28/2022#)) = Target Month Then
            FindFirstDDinMonth = DateAdd("d", i * IncrementDays, #12/28/2022#)
            Exit For
        End If
    Next i
End Function

This won't work, however, forever. I think the function needs to be recursive but I'm not sure.

Thanks very much for your help.

[ranman256]

i use a table with the dates in it for the year.
This avoids any calculations that could be wrong due to various events.

[Euler]

The answer is to use a Do...Until loop:

Function FindFirstDDinMonth(m As Integer, y As Integer) As Date
    Dim i As Integer
    Const IncrementDays As Integer = 14
    i = 1
    Do Until Month(DateAdd("d", i * IncrementDays, DirectDepositDate)) = m And Year(DateAdd("d", i * IncrementDays, DirectDepositDate)) = y
        i = i + 1
    Loop
    FindFirstDDinMonth = DateAdd("d", i * IncrementDays, DirectDepositDate)
End Function

Thanks for responding.