Function WeekNoToDate(StartDate As Date, WeekNo As Double) As String
Dim FirstDate As Date, SecondDate As Date
If WeekNo > 52 Or WeekNo < -52 Then
MsgBox "Maximum week no is + or - 52", vbCritical
Exit Function
End If
If Int(WeekNo) <> WeekNo Then
MsgBox "Week No must be a whole number", vbCritical
Exit Function
End If
FirstDate = DateAdd("ww", WeekNo, StartDate)
SecondDate = DateAdd("d", FirstDate, 6)
WeekNoToDate = "From:" & Format(FirstDate, "Medium Date") & " to:" &
Format(SecondDate, "Medium Date")
End Function
Watch how the StartDate is entered
WeekNoToDate(#1 apr 03#, 50) is OK
WeekNoToDate(#1/4/03#, 50) gives From:20-Dec-03 to:26-Dec-03 because it
assumes the date is 4th January.
Dates are all daft. Wouldn't it be much more logical to go yyyy/mm/dd. The
Brits have got it completely arse about face, and the Americans are even
more illogical with mm/dd/yyyy.
Phil
"Keith Wilby" <ke*********@AwayWithYerCrap.com> wrote in message
news:Xn************************@10.15.188.42...
ka*******@hotmail.com (Mark) wrote:
Hi
I have been trying to convert the week number to a range of dates that
I can use. It should be fairly simple for you guru's out there but for
us mere mortals it is beyond our grasp.
I know that there are different start days of the week so I would
presume any function would provide that facility. Hope you can help
Mark
Hi Mark,
This is a function I use to create a table which gives week number agains
a date range. You might be able to adapt this for your purposes. Note that
the date range in the code dictates the start day for each week (currently
a Monday).
HTH - Keith.
www.keithwilby.org.uk
Public Sub libMakeDate()
Dim db As Database
Dim td As TableDef
Dim fd As Field
Dim rs As Recordset
Dim dtmdate As Date
Set db = CurrentDb
' Create a new table
Set td = db.CreateTableDef("tblDate")
' Create a new field
Set fd = New Field
fd.name = "Date"
fd.Type = dbDate
' Add the field to the table
td.Fields.Append fd
' Create a new field
Set fd = New Field
fd.name = "WeekNo"
fd.Type = dbText
' Add the field to the table
td.Fields.Append fd
' Add the table to the database
db.TableDefs.Append td
' Open the table
Set rs = td.OpenRecordset
' Add dates and weekno
For dtmdate = #10/27/02# To #12/31/10# Step 7
rs.AddNew
rs!Date = dtmdate
rs!WeekNo = Year(dtmdate) Mod 10 & Right("0" & Format(dtmdate,
"ww"), 2)
rs.Update
Next
rs.Close
End Sub