Week number in vba

[Guest]

I need to find a way for vba to determine the week number of a given date in
a year. I do not want to depend on Excel's Analysis TookPak. Can anyone help?

 

[Gary Keramidas]

See
http://www.rondebruin.nl/weeknumber.htm


http://www.rondebruin.nl/weeknumber.htm#information

 

[Steve Yandl]

Sam,

The hard part is determining the reference date (when does week 1 begin for
the year in question?). Below is an example I helped someone with where his
industry considered week 1 to be the first full week in July with weeks
beginning on Sunday. This probably isn't the same as your requirement but
it might give a few hints.

__________________________________

Function WkCount(Optional dtmDate As Date)

' Test that an argument was actually received
If dtmDate = 0 Then
WkCount = ""
Exit Function
End If

Dim dtmRefJulOne As Date
Dim dtmTemp As Date
Dim intElapsedDays As Integer

' Determine most recent July 1st
If Month(dtmDate) < 7 Then
dtmRefJulOne = DateSerial(Year(dtmDate) - 1, 7, 1)
Else
dtmRefJulOne = DateSerial(Year(dtmDate), 7, 1)
End If

' Determine first Sunday in most recent July
dtmTemp = dtmRefJulOne
Do While Weekday(dtmTemp) <> vbSunday
dtmTemp = dtmTemp + 1
Loop

' Handle case for a date in July prior to the first Sunday in July
If Month(dtmDate) = 7 And dtmDate < dtmTemp Then
dtmRefJulOne = DateSerial(Year(dtmDate) - 1, 7, 1)
dtmTemp = dtmRefJulOne
Do While Weekday(dtmTemp) <> vbSunday
dtmTemp = dtmTemp + 1
Loop
End If

' Determine days elapsed since reference Sunday in July
' Divide by 7 and round up for a week count, format as integer
intElapsedDays = dtmDate - dtmTemp
WkCount = FormatNumber(CInt(Int((intElapsedDays / 7) + 1)), 0)

End Function

__________________________________

Steve Yandl

 

[Ron de Bruin]

I have also info on MSDN now Gary
http://msdn2.microsoft.com/en-us/library/bb277364.aspx

 

[Gary Keramidas]

ok, thanks for the link

--


Gary

I have also info on MSDN now Gary
http://msdn2.microsoft.com/en-us/library/bb277364.aspx

 

[Daniel.M]

Thanks Steve.

With the same idea but using date math instead of loops :


Function WeekCnt(Optional dtmDate As Date) As Variant

' Test that an argument was actually received
If dtmDate = 0 Then
WeekCnt = ""
Exit Function
End If

Dim July08 As Date, FirstSunJuly As Date

July08 = DateSerial(Year(dtmDate), 7, 8)
FirstSunJuly = July08 - Weekday(July08 - 1) ' 1st Sunday

If dtmDate < FirstSunJuly Then
July08 = DateSerial(Year(dtmDate) - 1, 7, 8) ' Correct Year
FirstSunJuly = July08 - Weekday(July08 - 1) ' 1st Sunday
End If

' Determine days elapsed since reference Sunday in July
' Divide by 7 and round up for a week count, format as integer

WeekCnt = Int(((dtmDate - FirstSunJuly) / 7) + 1)

End Function

Regards,

Daniel M.