14 dages periode over nytår

Hvordan når du frem til ugenummeret?

Jeg har det her kode: Det er den med fed der fejler.

Sub MaximumEffort_I()
    ' Declare variables
        Dim DateNow As Date
        Dim DateOfFirstMonday As Date
        Dim Weeks As String
        Dim StartDate As String
        Dim L As Integer
        Dim R As Integer
        Dim F As Integer
        L = 0
        R = 14
    ' Find Year
        Range("IndtastAar") = Format(Now(), "yyyy")
    ' Find "F" and "IndtastUge"
        Weeks = DatePart("ww", Now(), vbMonday, vbFirstFourDays)
            If Weeks Mod 2 = 0 Then
                Range("IndtastUge") = Weeks - 1 & " - " & Weeks
                F = 7
                Else
                Range("IndtastUge") = Weeks & " - " & Weeks + 1
                F = 0
            End If
    ' Find "DateOfFirstMonday" using "F"
        DateNow = (Format(Now(), "dd-mm-yyyy"))
        DateOfFirstMonday = DateNow + 1 - (Weekday(Now(), vbMonday)) - F
    ' Connect to SQL
        F_SQL_CONNECT
    ' Find "id_calender" and store as "StartDato"
        Set rs = New ADODB.Recordset
        sql_action = "SELECT id_calender FROM `one`.`calender` WHERE `date` = '" & DateOfFirstMonday & "'"
        rs.Open sql_action, conn_database, adOpenStatic
        StartDate = rs.Fields("id_calender")
        rs.Close
    ' Show dates from the "StartDate" + 14 next days
            While L < 14
                sql_action = "SELECT date FROM `one`.`calender` WHERE `id_calender` = '" & StartDate + L & "'"
                rs.Open sql_action, conn_database, adOpenStatic
                Worksheets("WorkHours").Range("B" & R & "") = rs.Fields("date")
                L = L + 1
                R = R + 1
                rs.Close
            Wend
    ' Show "starttime, stoptime, absence, notes" for each "date" where "initials" match
        L = 0
        R = 14
            Range("C14:D27").ClearContents
            Range("G14:I27").ClearContents
           
            While L < 14
                sql_action = "SELECT * FROM `one`.`worktime` WHERE `date` = '" & Range("B" & R & "") & "' AND `employee_id_employee` = '" & Range("IndtastID") & "'"
                rs.Open sql_action, conn_database, adOpenStatic
                If rs.RecordCount = 0 Then
                    ' skip
                    Else
                        Worksheets("WorkHours").Range("C" & R & "") = rs.Fields("starttime")
                        Worksheets("WorkHours").Range("D" & R & "") = rs.Fields("stoptime")
                        Worksheets("WorkHours").Range("G" & R & "") = rs.Fields("absence")
                        Worksheets("WorkHours").Range("H" & R & "") = rs.Fields("notes")
                    End If
                L = L + 1
                R = R + 1
                rs.Close
            Wend



    ' Disconnect from SQL
        F_MYSQL_DISCONNECT
End Sub

Hej

Jeg har en simpel løsning med en formel
indsæt nedenstående i kolonne A og dato i kolonne B. Datoen skal selvfølgelig indeholde dato måned og År. jeg bruger formatet  dd-mm-åååå

"=HVIS($U$5=53;UGE.NR(B11;2)-1;UGE.NR(B11))

m v h mads32