Thread: Sub routine for generating second week number of current fortnight pay period
View Single Post
 
Old 07-13-2025, 06:17 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 11 Office 2021
Expert
  
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,943
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default
Quote:
Originally Posted by Chris G View Post
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
FYI this forum has a dedicated VBA subforum
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote