There is not much consistency among the Excel functions and VBA functions, and the various "standards" used in business.  If you need to use week numbers, you need to understand how they work and what they represent.

Absolute Week Numbers

An absolute week number is the 7 day period that a date falls within, based solely on the first day of the year, regardless of the day of the week.  Week 1 is always Jan-1 to Jan-7, week 2 is always Jan-8 to Jan-14, and so on.  If the year begins on a Thursday, then each "week" is from Thursday to the following Wednesday. The absolute week number will always be between 1 and 53.   Week 53 will have either one or two days, depending on whether the year is a leap year.   If the year is a not a leap year, week 53 will consist of one day: Dec-31.  If the year is a leap year, week 53 will consist of two days:  Dec-30 and Dec-31.

Given a date in cell A1, you can determine the absolute week number with the following formula:

=TRUNC(((B6-DATE(YEAR(B6),1,0))+6)/7)

For example, if A1 contains the date 2000-June-21, the formula will return 25.  Since the year 2000 began on a Saturday, the 25th week runs from Saturday, 2000-June-17, to Friday, 2000-June-23.

Excel Week Numbers

Excel itself does not have a function for returning a week number.  The Analysis ToolPak add-in module does have a WEEKNUM function.  This function return the week number of a date, based on a week starting on either Sunday or Monday.   Therefore, week 1 may have anywhere from 1 to 7 days.  For example, if you set the return type of WEEKNUM to 2, indicating that weeks begin on Monday, and the year is 2001, which begins on a Monday, week 1 will have 7 days.  But for the year 2006, which begins on a Sunday, week 1 will have only 1 day.  For example, if A1 contains the date 2000-Jan-9, a Sunday, the function

=WEEKNUM(A1,2)

will return 2, as 2000-Jan-9 is the last day of the second week of 2000.  Since the 2 in the formula indicates that weeks begin on Monday, week 2 is computed as Monday, 2000-Jan-3 through Sunday, 2000-Jan-9. If we change the formula to 

=WEEKNUM(A1,1)

it will return 3, since the 1 in the formula indicates that weeks begin on Sunday, and week 3 is computed as Sunday, 2000-Jan-9, through Saturday, 2000-Jan-15.

For the year 2000,  WEEKNUM will compute week 1 as containing either only 1 day, Saturday, 1-Jan, if the return-type setting is 1 (weeks begin on Sunday), or as containing 2 days, Saturday, 1-Jan, and Sunday, 2-Jan, if the return-type setting is 2 (weeks begin on Monday).

You can achieve the same result in VBA code with the following function: 

Function VBAWeekNum(D As Date, FW As Integer) As Integer
     VBAWeekNum = CInt(Format(D, "ww", FW))
End Function

The FW parameter serves the same purpose here as it does in the WEEKNUM  worksheet function.  Set it to 1 to indicate that weeks begin on Sunday, or to 2 to indicate that weeks begin on Monday.

ISO Week Numbers

The International Organization for Standardisation, based in Switzerland, issued Standard 8601 -- Representation Of Dates And Times, in 1988.  This provides some standardization for "week numbers".  Of course, compliance with these standards is entirely voluntary, so your business may or may not use the ISO definitions.  

Under the ISO standard, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.  

While this provides some standardization, it can lead to unexpected results -- namely that the first few days of a year may not be in week 1 at all.  Instead, they will be in week 52 of the preceding year!  For example, the year 2000 began on Saturday.  Under the ISO standard, weeks always begin on a Monday.  In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3.  Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.   

An ISO week number may be between 1 and 53.   Under the ISO standard, week 1 will always have at least 4 days.  If 1-Jan falls on a Friday, Saturday, or Sunday,  the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.

Unlike absolute week numbers, not every year will have a week 53.  For example, the year 2000 does not have a week 53.   Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec.   But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec.  

To compute the ISO week number of a date in cell  A1, use the following formula, provided by Laurent Longre. Note that this formula will not always return the correct value if you are using the 1904-based date system. 

=INT((A1-SUM(MOD(DATE(YEAR(A1-MOD(A1-2,7)+3),1,2),
{1E+99,7})*{1,-1})+5)/7) 

The following formula, developed by Evert van den Heuvel, works with either the 1900 or 1904 based date system.

=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+
WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,3)))/7)
 

In VBA, you can use the following function, written by John Green.  

Public Function ISOWeekNum(AnyDate As Date, _ 
     Optional WhichFormat As Variant) As Integer
' 
' WhichFormat: missing or <> 2 then returns week number,
'              = 2 then YYWW
'
Dim ThisYear As Integer
Dim PreviousYearStart As Date
Dim ThisYearStart As Date
Dim NextYearStart As Date
Dim YearNum As Integer

ThisYear = Year(AnyDate)
ThisYearStart = YearStart(ThisYear)
PreviousYearStart = YearStart(ThisYear - 1)
NextYearStart = YearStart(ThisYear + 1)
Select Case AnyDate
    Case Is >= NextYearStart
        ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
        YearNum = Year(AnyDate) + 1
    Case Is < ThisYearStart
        ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1
        YearNum = Year(AnyDate) - 1
    Case Else
        ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
        YearNum = Year(AnyDate)
End Select

If IsMissing(WhichFormat) Then 
    Exit Function
End If
If WhichFormat = 2 Then
    ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _
    Format(ISOWeekNum, "00"))
End If

End Function

This function requires the YearStart function, also written by John Green, which returns the date of the first Monday of a given year.

Function YearStart(WhichYear As Integer) As Date

Dim WeekDay As Integer
Dim NewYear As Date

NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7 
If WeekDay < 4 Then
    YearStart = NewYear - WeekDay
Else
    YearStart = NewYear - WeekDay + 7
End If

End Function

Summary

As you can see, Week Numbers are not as simple as they may appear on the surface.  They may be useful for some business applications, but it is important that you understand just what is being calculated, and what it really means.   If your business uses week numbers, make sure you understand what the basis for week 1 represents.

Julian Dates

Latitude And Longitude 

Overtime Hours And Timesheets

Time Zones In VBA

VBA Procedures For Dates And Times

Week Numbers

Worksheet Functions For Dates And Times

See the Dates And Times Topic Index For Information