Custom WeekNumber

S

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
 
W

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
 

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