VBA Function to get the financial week number - year starting on July

[Guinaba]

Hi guys,

Trying to create a function that gives me the financial week number based on the week number, the financial week number start on July. However I keep getting the incorrect week number everytime the new year starts, for example in the pic below the Wk# is jumping from wk53 (2020) to wk2 when moving to new year, maybe week number is not a good idea to get the financial week number. My weeks start on Sundays. Any suggestions?

2020 -> 2021

2021 -> 2020

Option Explicit

Function FWkNum(InputDate As Date) As String
   
   Dim WeeksInYear As Long
   WeeksInYear = DatePart("ww", DateSerial(Year(InputDate), 12, 28), vbSunday, vbFirstFourDays) '28 December is always in the last week of its year.
   Dim InWk As Long
   InWk = WorksheetFunction.WeekNum(InputDate, vbSunday)

    If WeeksInYear <= 52 Then 'Number of weeks in the year
       If InWk <= 26 Then 'Wks before the financial year (Jul)
        FWkNum = WorksheetFunction.WeekNum(InputDate, vbMonday) + 26
      Else
        FWkNum = WorksheetFunction.WeekNum(InputDate, vbMonday) - 25
      End If
    Else 'year with 53 weeks
      If InWk <= 26 Then 'Wks before the financial year (Jul)
        FWkNum = WorksheetFunction.WeekNum(InputDate, vbMonday) + 26
      Else
        FWkNum = WorksheetFunction.WeekNum(InputDate, vbMonday) - 25
      End If
    End If
   
End Function
Sub Test()

  Dim InputDate As Date
  Dim WeeksInYear As Long
    
    InputDate = ActiveSheet.Range("d29").Value
    ActiveSheet.Range("f29").Value = FWkNum(InputDate)
     
End Sub

 

[Crystalyzer]

what if you change this code to check for <=53

If WeeksInYear <= 53 Then 'Number of weeks in the year

 

[Guinaba]

what if you change this code to check for <=53

If WeeksInYear <= 53 Then 'Number of weeks in the year

Hi @Crystalyzer, I have tried already, but didnt worked

 

[Crystalyzer]

The WEEKNUM function is correct given a week would start on Sunday for the dates you list above. If you change the dates to 1/1/yy you will see that the WEEKNUM returns 1. This is because the dates fall on a Friday or Saturday respectively and the new week (#2) starts on Sunday.