UDF Period Number

[Paddy1979]

Hi folks,

I'm trying to build a UDF which promts the user to select a date then the function will return the financial Period Number.

This is my first attempt at building a UDF so apologies if its pretty woeful.

The code is crashing when it converts the date into a weeknumber, any ideas?

Val = Weeknum(Date_convert, 2)

Function Periodnumber(ByVal Date_convert)

Dim p As Integer

If Val(Date_convert) = 0 Then Exit Function

Val = Weeknum(Date_convert, 2)

Select Case Val
 Case 1 To 4
  p = 1
 Case 5 To 8
  p = 2
 Case 9 To 13
  p = 3
 Case 14 To 17
  p = 4
 Case 18 To 21
  p = 5
 Case 22 To 26
  p = 6
 Case 27 To 30
  p = 7
 Case 31 To 34
  p = 8
 Case 25 To 39
  p = 9
 Case 40 To 43
  p = 10
 Case 44 To 27
  p = 11
 Case 48 To 52
  p = 12
End Select
 
Periodnumber = "P" & p
   
End Function

Many thanks
Patrick

 

[Richard Schollar]

Hi Patrick

Don't use a function name (ie Val) as a variable name - you're asking for trouble that way.

 

[Paddy1979]

Hi Richard,

Thanks for the Tip. Changed code to

i = weeknum(Date_convert, 2)

but still no luck

 

[pgc01]

Hi Patrick

Why not just a formula?

=MATCH(WEEKNUM(A1,2),{1,5,9,14,18,22,27,31,35,40,44,48})

But, if you really want for some reason to use it in vba, then

weeknum() is not a vba function

use:

iWeekNum = Evaluate("Weeknum(" & CLng(Date_convert) & ", 2)")

Remarks:
What happens in week 53?
Check the periods' boundaries, periods 9 and 11 have wrong week numbers.

 

[Paddy1979]

Hi PGC

Thanks for the advice much apreciated, i thought thats why its was not accepting the weeknum function.

1/12/2007 , would be P1 W1.

I think i have cracked it with the below it seems to work correctly on testing. Edit correction its not working correctly

Public Function PERIOD_NUMBER(date_here As Date) As String

    Dim date_enter As Long
    
    date_enter = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
    week_number = Int((d1 - d2 + Weekday(d2) + 5) / 7)
    
    Select Case week_number
        Case 1 To 4
        p = 1
        Case 5 To 8
        p = 2
        Case 9 To 13
        p = 3
        Case 14 To 17
        p = 4
        Case 18 To 21
        p = 5
        Case 22 To 26
        p = 6
        Case 27 To 30
        p = 7
        Case 31 To 34
        p = 8
        Case 25 To 39
        p = 9
        Case 40 To 43
        p = 10
        Case 44 To 27
        p = 11
        Case 48 To 52
        p = 12
    End Select
    
    PERIOD_NUMBER = "P" & week_number
    
    
End Function

 

[pgc01]

Edit correction its not working correctly

This one works for me:

Function Periodnumber(ByVal Date_convert As Date)
Dim p As Integer, iWeekNum As Integer

If Val(Date_convert) = 0 Then Exit Function

iWeekNum = Evaluate("Weeknum(" & CLng(Date_convert) & ", 2)")

Select Case iWeekNum
 Case 1 To 4
  p = 1
 Case 5 To 8
  p = 2
 Case 9 To 13
  p = 3
 Case 14 To 17
  p = 4
 Case 18 To 21
  p = 5
 Case 22 To 26
  p = 6
 Case 27 To 30
  p = 7
 Case 31 To 34
  p = 8
 Case 25 To 39
  p = 9
 Case 40 To 43
  p = 10
 Case 44 To 27
  p = 11
 Case 48 To 52
  p = 12
End Select

Periodnumber = "P" & p & " W" & iWeekNum

End Function

Also this one:

Function Periodnumber(ByVal Date_convert As Date)
Dim p As Integer, iWeekNum As Integer

If Val(Date_convert) = 0 Then Exit Function

iWeekNum = Evaluate("Weeknum(" & CLng(Date_convert) & ", 2)")

Periodnumber = "P" & Application.WorksheetFunction. _
    Match(iWeekNum, Array(1, 5, 9, 14, 18, 22, 27, 31, 35, 40, 44, 48)) & _
    " W" & iWeekNum

End Function

Although I would use the formula:

=MATCH(WEEKNUM(A1,2),{1,5,9,14,18,22,27,31,35,40,44,48})

 

[Paddy1979]

Hi PGC,

Its just what i needed, the formula is clever too.

Thanks for your time.

Paddy

 

[barry houdini]

What happens in week 53?

Weeknum(date,2) also can give a week 54, e.g. in 2012. [so using the above would make period 12 in 2012 43 days long]

If the aim is to split the year into periods of exactly 4, 4 and 5 weeks then I wouldn't use WEEKNUM function because, using that, weeks at the start and end of the year can have as little as 1 day. Probably better to use ISO week numbers where all weeks have 7 days (week 1 always starts on the first Monday on or after 29th December)

 

[jon_k]

4-4-5 is a pain: if you can spare the calculation time, I'd just knock the dates into a vlookup(x,y,z,true)