Microsoft Office Forums - View Single Post - Sub routine for generating second week number of current fortnight pay period

Chris G · 07-07-2025, 07:51 PM

Is this the best way to generate in a future proof way the week number, representing whatever is the SECOND week of the current (fortnightly) pay period?

This code below is on a command button click event on a sheet in a timesheet template, and having tried a few different options, this gives the correct result today, July 8 2025, of 28. A lot of dates get updated from this value, and while cell U4 also has a drop down list, it would be nice that it always worked from a command button.

Code:

    Dim referenceStart As Date
    Dim currentDate As Date
    Dim daysSinceReference As Long
    Dim fortnightIndex As Long
    Dim fortnightStart As Date
    Dim secondWeekStart As Date
    Dim isoWeek As Integer

    ' Reference start date (must be a known Monday that starts a fortnight)
    referenceStart = DateSerial(2025, 7, 1) ' Tuesday 1 July 2025 — adjust to Monday
    If Weekday(referenceStart, vbMonday) <> 1 Then
        referenceStart = referenceStart - Weekday(referenceStart, vbMonday) + 1 ' Adjust to nearest previous Monday
    End If

    currentDate = Date
    daysSinceReference = currentDate - referenceStart
    fortnightIndex = Int(daysSinceReference / 14)
    fortnightStart = referenceStart + (fortnightIndex * 14)
    secondWeekStart = fortnightStart + 7

    ' Get ISO week number
    isoWeek = Application.WorksheetFunction.IsoWeekNum(secondWeekStart)

    ' Output to cell U4
    Worksheets("Fortnightly timesheet").Range("U4").Value = isoWeek