Calculate Week Number

[JUANCARLOS]

I want a Function to calculate the number of the date as Outlook.

Conditions:
- The Week number 1 always contents the 01/january
- The Week number 1 also contains the days of the past year until find a Monday
- The week runs from Monday to Sunday.

In the 2000 is a special year such 2006, etc., this exist the 53 week, in others years has 52 weeks.

Please help me, thanks in advance.

JCHR

 

[gerard1979]

I think write some code looking to the date functions.
In a few weeks I need something like this, so I give a look on it. It's no problem, but use smart functions in Access on a smart way will be enough ;c)
I will come back with an idea in the near future,
maybe other users know more details.

Later,
gerard

 

[gerard1979]

1 question:
u need something like
function datum(week as integer) gives
?datum(51)
12-12-02 t/m 19-12-02
or
function week(datum as date)
?week(14-12-02)
51

 

[MichaelRed]

? DatePart("ww", Now)
41

DatePart is an 'intrinsic' (e,g, BUILY IN) function. My "example does NOT include the firstdayofweek argument, which you would probably set to vbMonday, but that is an exercise left to your persual and understanding of the function via liberal doses of "Help" and other documentation.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[gerard1979]

I give u a little try
only 3 minutes work so it's not errortrapped or efficient but the idea is there
u can also go to the other side with the format as Micheal says.
Public Function GeefDatum(week As Integer) As String
Dim dateStr, constWeek1 As String
Dim Jan1, week1 As Date
If week < 1 Or week > 52 Then
MsgBox &quot;Give weeknumber between 1 and 52&quot;
Else
dateStr = &quot;1-1-&quot; & Year(Now)
'define first week
week1 = CDate(dateStr) - Weekday(CDate(dateStr), vbMonday) + 1

week1 = week1 + (week - 1) * 7
GeefDatum = CStr(week1 & &quot; t/m &quot; & week1 + 6)
End If
End Function