Weeknum UDF help

R

Ray

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top