WeekNum function not returning correct value

[AndyEd]

Can someone please help. I have the following which I am using to calculate the Week Number is respect of the current date, Today().

With ThisWorkbook.Worksheets("Tracker") ' Select workbook and sheet.
        .Cells(.Rows.Count, "M").End(xlUp).Offset(0, 1) = Application.WorksheetFunction.WeekNum(ThisWorkbook.Worksheets("Tracker") _
        .Cells(.Rows.Count, "M").End(xlUp).value - WeekNum(Date))
    End With

I seem to be having an issue with the 2nd half of the function, from " -WeekNum(Date())". When displayed as above it returns the following error:

Comile error:
Sub or Function not defined

If I change to " - Application.WorksheetFunction.WeekNum(Date())" for a date of 29/01/2024 it returns "4," rather than "-3", which is what I am hoping to get.

As always, with appreciation.

 

[FormR]

Hi, you have a misplaced parentheses:

With ThisWorkbook.Worksheets("Tracker") ' Select workbook and sheet.
    .Cells(.Rows.Count, "M").End(xlUp).Offset(0, 1) = Application.WorksheetFunction.WeekNum(ThisWorkbook.Worksheets("Tracker") _
    .Cells(.Rows.Count, "M").End(xlUp).Value) - Application.WorksheetFunction.WeekNum(Date)
End With

You could also use the VBA's native DatePart() function to get the week, for example:

With ThisWorkbook.Worksheets("Tracker")
    With .Cells(.Rows.Count, "M").End(xlUp)
        .Offset(0, 1).Value = DatePart("WW", .Value) - DatePart("WW", Date)
    End With
End With

 

[AndyEd]

Thank you FromR, and for the alternative.