Custom WeekNumber

[Scott]

I'm trying to write a function that returns the week number when passed a
date. The trick is the beginning date of the year will usually be in
December right afer Christmas and may change year to year. Below is my code
that should allow me to pass sDate (which could be any date in the coming
year, sBeginYearDate which would be the companies 1st day of fiscal year and
sWeeksout being how many weeks the fiscal year will contain.)

Could someone try this out in VBE and run it with something like:

WeekNumber(#1/5/2004#, #12/28/2003#, 52)

in the immediate wndow? I'm real close to getting it, but must be missing
something in my logic. If anyone can think of a better way, I'm all ears.

================================
Function WeekNumber(sDate As Date, sBeginYearDate As Date, sWeeksOut As
Integer) As Integer

Dim sSunday As Date, sSaturday As Date, i As Integer
i = 1

sSunday = (sBeginYearDate - DatePart("w", sBeginYearDate)) + 1
sSaturday = sSunday + 6

Do While (i <= sWeeksOut)

If sDate >= FormatDateTime(sSunday, vbShortDate) And sDate <=
FormatDateTime(sSunday, vbShortDate) Then
WeekNumber = i
End If
Loop
End Function

 

[Wayne Morgan]

Just a quick try, but I believe this will work.

Function WeekNumber(sDate As Date, sBeginYearDate As Date, sWeeksOut As
Integer) As Integer

Dim sSunday As Date, sSaturday As Date, i As Integer
i = 1

sSunday = (sBeginYearDate - DatePart("w", sBeginYearDate)) + 1
sSaturday = sSunday + 6

Do While (i <= sWeeksOut)

If sDate >= FormatDateTime(sSunday, vbShortDate) And sDate <=
FormatDateTime(sSaturday, vbShortDate) Then
WeekNumber = i
Exit Function
End If
i = i + 1
sSunday = sSunday + 7
sSaturday = sSaturday + 7
Loop
End Function

 

[Mike Sherrill]

I'm trying to write a function that returns the week number when passed a
date. The trick is the beginning date of the year will usually be in
December right afer Christmas and may change year to year.

The real trick is to store business data--like week numbers--in a
table.