Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    112

    Unanswered: Convert a date to a week number

    Anyone know what the code/formula is to convert a date in to a week number? eg Thursday 9th September = Wk 37

    Thankyou.

  2. #2
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    Hi again!
    A couple of month ago, I dealed with the same problem. Next function returns the weeknumber of the entered date. Function is based on the fact that the 4th of jan is always in week 1.
    Code:
    Public Function Week(dteDate As Date) As Integer
        'Calculates the week number for selection criteria
        
        Dim FirstDayWeek1 As Date
        Dim Jan4 As Date
        Dim Difference As Integer
        
        'Look after next year. For correct week number, 
        'you should know the first day of week 1.
        '(is not always 1th jan!! The week which contain
        '4 jan is always week 1)
        Jan4 = DateSerial(Year(dteDate) + 1, 1, 4)
        
        FirstDayWeek1 = 1 + Jan4 - Weekday(Jan4, vbMonday)
        
        If dteDate >= FirstDayWeek1 Then
            'input date falls in week 1 next year
            Week = 1
            Exit Function
        End If
        
        'Watch this year...
        Jan4 = DateSerial(Year(dteDate), 1, 4)
        
        FirstDayWeek1 = 1 + Jan4 - Weekday(Jan4, vbMonday)
        
        'Watch to next year
        If dteDate < FirstDayWeek1 Then
            ' Is the last week of last year
            Jan4 = DateSerial(Year(dteDate) - 1, 1, 4)
            FirstDayWeek1 = Jan4 - Weekday(Jan4, vbMonday)
        End If
        
        Difference = dteDate - FirstDayWeek1
        Week = 1 + Difference \ 7
        
    End Function
    A good developer is a lazy developer - don't be to lazy, share your knowledge

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    or just use datepart("ww",thedate)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    Quote Originally Posted by r937
    or just use datepart("ww",thedate)
    You can not really trust this function.
    datepart("ww",#01-01-2005#) gives week 1 and it should be week 53
    A good developer is a lazy developer - don't be to lazy, share your knowledge

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •