UDF Period Number

Paddy1979

Well-known Member
Joined
Sep 23, 2005
Messages
608
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)

Code:
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Patrick

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

Paddy1979

Well-known Member
Joined
Sep 23, 2005
Messages
608
Hi Richard,

Thanks for the Tip. Changed code to

Code:
i = weeknum(Date_convert, 2)

but still no luck
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,877
Hi Patrick

Why not just a formula?

Code:
=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:

Code:
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

Well-known Member
Joined
Sep 23, 2005
Messages
608

ADVERTISEMENT

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

Code:
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,877
Edit correction its not working correctly

This one works for me:
Code:
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:
Code:
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:
Code:
=MATCH(WEEKNUM(A1,2),{1,5,9,14,18,22,27,31,35,40,44,48})
 

Paddy1979

Well-known Member
Joined
Sep 23, 2005
Messages
608

ADVERTISEMENT

Hi PGC,

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

Thanks for your time.

Paddy :)
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Mar 13, 2007
Messages
59
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top