Get WeekNumber from weeks starting on Sunday 1

[RayDance]

'**************************************
' Name: Get WeekNumber from weeks starting on Sunday
' Description:This function calculates the weeknumber of a date for weeks starting on a sunday. The Microsoft function WeekNumber(ww, date) calculates weeknumbers for weeks starting on a Monday!
'
' Inputs:dDatum = the date to Calculate with
'
' Returns:It returns the weeknumber from the given date.
'
'Side Effects:It is only for weeks starting on a sunday!
'This code is copyrighted and has limited warranties.
'**************************************



Public Function Sunday_WeekNR(ByVal dDatum As Date) As Integer
Dim Weeknr As Integer
Dim beginDate As Date
Dim EndDate As Date

'NL: Bereken de zondag van de opgegeven week(datum)
'ENG: Calculate sunday from given week(date)
Do While Weekday(dDatum) <> vbSunday
dDatum = DateAdd("d", -1, dDatum)
Loop
beginDate = DateSerial(Year(dDatum), 1, 1)
EndDate = DateSerial(Year(dDatum), 12, 31)

'NL: Bereken start week
'ENG: Calculate start week from year
If Weekday(beginDate) <= 5 Then
Weeknr = 1
Else
Weeknr = 53
End If

'NL: Zet de startdag van de Beginweek op zondag
'ENG: Set Startday of beginweek on sunday
Do While Weekday(beginDate) <> vbSunday
beginDate = DateAdd("d", -1, beginDate)
Loop


If beginDate < DateSerial(Year(dDatum), Month(dDatum), Day(dDatum)) Then
Do
If Convert(Weekday(beginDate)) = 7 Then
If Weeknr = 54 Then
Weeknr = 1
End If
Weeknr = Weeknr + 1
If Weeknr > 52 And (Weekday(EndDate) < 5) Then
Weeknr = 1
End If
End If
'beginDate = DateAdd("d", 1, beginDate)
beginDate = DateAdd("d", 7, beginDate)
Loop Until (beginDate >= DateSerial(Year(dDatum), Month(dDatum), Day(dDatum)))
End If

If Weeknr = 54 Then
Weeknr = 1
End If
Sunday_WeekNR = Weeknr
End Function

Public Function Convert(x)
Select Case x
Case 1
Convert = 7
Case Else
Convert = x - 1
End Select
End Function


Greetz From,
** RayDance **

Hi..di..hi..di..ho, it's just a programmers Role

 

[strongm]

1) VB doesn't have a WeekNumber function ...
2) Why don't we just use the somewhat simpler:

Format(Now, "ww",vbSunday, vbFirstJan1)

which will return the correct week as per the ISO 8601 standard ...

 

[RayDance]

strongm,

If a week starts on a sunday and you use Format(Now, "ww",vbSunday, vbFirstJan1), then you get the wrong weeknumber for "1-jan-2005". "1-jan-2005" is week 53 of 2004 and not week 1 of 2005!! It is a bug in a Microsoft DLL! You can find this everywhere on the internet and on the MS site!!

Try it!


Greetz From,
** RayDance **

Hi..di..hi..di..ho, it's just a programmers Role

 

[strongm]

Yes, I know, and we've had long discussions about this in the past on this forum.

 

[johnwm]

For a clearer understanding of ISO 8601 see:

http://www.cl.cam.ac.uk/~mgk25/iso-time.html

where the definition of week numbers is clarified

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[strongm]

Oops, and meant to add in my last post:

Format(Now, "ww",vbMonday,vbFirstFourDays)

 

[RayDance]

strongm,

The discussion isn't over yet!

Because:
MsgBox Format("3-jan-2010", "ww", vbMonday, vbFirstFourDays)
Gives week 53 of 2009, but must be week 1 of 2010!!

See the following article on how computers determine a week, how to get around this MS bug & when the MS bug can occur.

http://support.microsoft.com/kb/q200299/

But I made my one solution which stands above and is more easy to understand!


Greetz From,
** RayDance **

Hi..di..hi..di..ho, it's just a programmers Role