Weeknum (2007)

[Egg 'n' Bacon]

Hi, I know in previous versions (2000/03) the weeknum function was flawed & the module ISOweeknum was posted (by Hans, I believe).

I'm just wondering if the issue has been resolved in 2007 & if not how do I get the code to work

Code: Select all

Function ISOWeekNum(aDate As Date) As Integer
    Dim datJan4 As Date
    Dim datWeek1 As Date
    Dim intYear As Integer
    Dim intDayOfWeek As Integer
    
    For intYear = Year(aDate) + 1 To Year(aDate) - 1 Step -1
        datJan4 = DateSerial(intYear, 1, 4)
        intDayOfWeek = Weekday(datJan4, vbUseSystemDayOfWeek)
        datWeek1 = datJan4 + 1 - intDayOfWeek
        If aDate >= datWeek1 Then
            Exit For
        End If
    Next intYear
    ISOWeekNum = (aDate - datWeek1) \ 7 + 1
End Function

[HansV]

The WEEKNUM function hasn't been changed in Excel 2007 - it uses the USA convention for week numbering.

There's a simpler version than the one I posted in Woody's Lounge long ago - this one is by Chip Pearson (Week Numbers In Excel):

Code: Select all

Public Function ISOWeekNumber(InDate As Date) As Integer
    Dim D As Date
    D = DateSerial(Year(InDate - Weekday(InDate - 1) + 4), 1, 3)
    ISOWeekNumber = Int((InDate - D + Weekday(D) + 5) / 7)
End Function

You can copy this function into a module in your personal macro workbook Personal.xlsb:

x119.png

You can then use it in any workbook. Let's say that you enter a date in cell A1. Enter the following formula in B1:

=Personal.xlsb!ISOWeekNumber(A1)

This will return the week number according to the EU convention:

x120.png

[Egg 'n' Bacon]

That looks good.

Couple of questions though;
Will this need to be in every users personal.xlsb (if so where is the file normally found) and
If not, how can I activate the function (currently just get #name#)



EDIT; it's OK, I was putting the code in another sheet that I had open - Doh!

[HansV]

If you want to make this function available to other users, you have several options:

• If you need it only in a specific workbook, you can copy the code into a module in that workbook.
  The formula then becomes even simpler:
  
  =ISOWeekNumber(A1)
  
  Since the function is stored in the workbook itself, it will be available to anyone who opens the workbook. It will not be available in other workbooks. though.
• If you want other users to be able to use the function in all their workbooks, you could ask them to copy the code into their personal macro workbook Personal.xlsb. You should *not* overwrite their Personal.xlsb with your version - that would destroy their own macros, if they have any.
  A better option would be to create an Excel add-in and let users install it.
  I have attached such an add-in (zipped).
  
  WeekNum.zip
  Extract the WeekNum.xlam file to any folder (the default location is C:\Users\<username>\AppData\Roaming\Microsoft\AddIns in Windows Vista/7, or C:\Documents and Settings\<username>\Application Data\Microsoft\AddIns in Windows XP).
  To install (or uninstall) an add-in, click the Office button and click Excel Options.
  Select Add-Ins in the pane on the left hand side.
  Click the Go... button next to Manage Excel Add-ins.
  If WeekNum is already listed, tick its check box. Otherwise click Browse... and find WeekNum.xlam.
  Once the add-in has been installed, the user can use the function in a formula such as
  
  =ISOWeekNumber(A1)

HTH

[Egg 'n' Bacon]

That is SO good

Though I think I'll just stick it in the individual files.

One thing that has arisen; is this year's numbers; in WeekNum, Outlook and on our year planner, the number of this week is 19. But Using ISOWeekNum(ber) this results in 18

[HansV]

By default, Outlook uses the US week numbering convention. If you want to use ISO 8601, select Tools | Options... and click Calendar Options...
The setting for ISO week numbers is "First 4-day week".

x121.png

Today (7 May 2010) will then be in week 18.

If your year planner has week 19, it probably also uses the US convention...

[Egg 'n' Bacon]

Ah that makes sense now. Thank you for your patience