convert date to calender week

[snoopy]

he guys I have a query with date, but I need the calender week. Do you have any advises for me how I could convert from date to calender week inside a query?

[Rod]

You need to write a public function that accepts a date as an argument and returns the calendar week. Place the function in a standard module. When using the function in SQL do not qualify it with the module name.

For example suppose the function is called CalcCalWeek and your date field is called MyDate then the design grid specification is:

CalendarWeek: CalcCalWeek([MyDate])

or the in-line SQL

CalcCalWeek([MyDate]) As CalendarWeek

[snoopy]

Its really urgent,

do you have any code examples for me

thanks in advance

[Rod]

No because each and every organisation divides the calendar in different ways. Week1 for you may not be week 1 for me.

[snoopy]

I created my own. First I extracted the access query to Excel and then I collected the following vba-code

Public Function IsoWeekNumber(d1 As Date) As Integer
Dim d2 As Long
If IsNull(d1) Then
IsoWeekNumber = 0
End If
d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
IsoWeekNumber = Int(d1 - d2 + Weekday(d2) + 5) / 7
End Function

So, actually there should be no internal company guideline for converting calender weeks, because there is a standard by ISO. There are also some standards by national organisations. Why to reinvent the wheel, anyway
That is one main reason for using standards.

After it you just use this function in your excel sheet. Attention you need two worksheets, one with date as coming from database query and one, which converts into calender week.

here is the code for the field:

=IF(Table1[@[ROW]],IsoweekNumber(Table1[@[ROW]]) & "CW / " & YEAR(Table1[@[ROW]]),"")

That's it

[Rod]

Snoopy thanks for pointing out the ISO standard.

ISO 8601 was introduced in 1988, and subsequently modified in 2000 and 2004. The year 1988 was after the last time I worked with week numbers so no wonder I was unaware of the standard. I'm still puzzled as to whether the Muslim nations follow this standard. Anyone know?

Glad to see you have it working in Excel. Didn't you originally want an Access function?

PS - Later

It's all there:

DatePart("ww", dte, vbMonday, vbFirstFourDays)

[orange]

Just to add a reference (C Pearson's site http://www.cpearson.com/excel/WeekNumbers.aspx)

ISO Week Number

This method uses the International Standards Organization (ISO) definition of a week number. An ISO week always begins on a Monday, and ISO week 1 begins on the Monday of the first week of the year that contains a Thursday. Said differently, ISO week 1 is the first week (beginning on Monday) that has at least four days.
Note that it is possible that the first few days of a year may fall into week 52 or week 53. Although this may seem quite counter intuitive, it follows from the requirements first that all ISO weeks begin on Monday, and second, that the first week must contain a Thursday. For example, the year 2010 begins on Friday, 1-Jan-2010. Since this is later than a Thursday (which is 7-Jan-2010), the week doesn't contains Thursday (or, said another way, the week doesn't have at least four days), the ISO week 1 begins on Monday, 4-January-2010. The day before the start of the year, 31-December-2009, falls in ISO week 53 of the preceeding year, 2009. Rather than deal with the ambiguity of having a day that is in both week 53 and week 0, the 53 carries through until 4-Jan-2010, which as noted before is the Monday of the first week that contains a Thursday (or, equivalently, the first week with four or more days).
The VBA code for calculating an ISO week number is shown below:

Public Function IsoWeekNumber(InDate As Date) As Long
IsoWeekNumber = DatePart("ww", InDate, vbMonday, vbFirstFourDays)
End Function