I'm using a UDF (found in this newsgroup!) to calculate the week
number from a user-entered date ... the code for this UDF is below.
All works ok except when the date entered is a Sunday -- this is the
first day of the week, but the UDF calcs it as part of the previous
week.

I tried both 'WeekStart' options, neither one gives the right answer.
As example, June 21 should come back as week 26. I'm using Excel 07
(Compatability mode) on Windows XP.

Any ideas on what needs to be changed (if anything)?

Thanks,
Ray


Function WKNUM(TheDate As Date, WeekStart As Integer)

' Using the 1900 date system.
' WeekStart = 1 Week starts on Sunday
' WeekStart = 2 Week starts on Monday

Dim TempDate As Date
Dim ExtraDays As Integer

If WeekStart < 1 And WeekStart < 2 Then
WKNUM = "#NUM!"
Exit Function
End If

TempDate = TheDate + 4 - Weekday(TheDate + 1 - WeekStart)
ExtraDays = (Weekday(DateSerial(Year(TempDate), 1, 2 - WeekStart))
+ 2) Mod 7
WKNUM = Int((TempDate - DateSerial(Year(TempDate), 1, 1) +
ExtraDays) / 7) + 1

End Function