Sub routine for generating second week number of current fortnight pay period

[Chris G]

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.

    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

[ArviLaanemets]

My advice is - have a calendar sheet in your workbook, and use it to calculate any dates matching any criterions you need.

The calendar sheet must have all dates as 1st column, from some start date until some future date (probably about 5 or 10 years in advance). And it may have any number of additional columns, where some characteristics are calculated when the table is created, and then saved as values. Like weekday number, week number (in format yyyyww), month number (in format yyyymm), a column which indicates state holidays (1 for holiday, 0 otherwise), etc.

Now, for your current task, you need to use VLOOKUP() function to calculate a week number from calendar table at date ReferenceDate+n*7 (where n is number of weeks you want to add)

Or, in case you want instead a date of specific weekday returned, you need to use SUMIFS() function to calculate the date in week your referenceStartDate belongs to (with referenceDate and weekday number as conditions), and add n*7 days to result.

And format the column with formula in your table as date.

[Chris G]

Thanks for answering my query.

Your suggestion would definitely be a way to input public holiday data for many years ahead, and use that to modify the data in a timesheet worksheet. I might look into doing that.

As for a formula for generating the week number of the second week for any fortnight during the year, I've since discovered this seems to work:

=ISOWEEKNUM(A2 + (MOD(ISOWEEKNUM(A2)-1,2)=0)*7)

Simply having a =NOW() formula in cell A2 to refer to, another cell with the above formula seems to return the correct value.

Cheers.

[Pecoflyer]

Originally Posted by Chris G

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.

    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

FYI this forum has a dedicated VBA subforum