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
07-07-2025, 07:51 PM
Sub routine for generating second week number of current fortnight pay period
Similar Threads
Find number and words without a period and bold
Windows 11 
Office 2016
Threaded Mode