# Thread: Convert a date to a week number

1. Registered User
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. Registered User
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

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
or just use datepart("ww",thedate)

4. Registered User
Join Date
May 2004
Location
The Netherlands
Posts
52
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

#### Posting Permissions

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