468,301 Members | 1,444 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,301 developers. It's quick & easy.

Convert Week Number To Date Range

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
Nov 12 '05 #1
4 21572
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
Nov 12 '05 #2

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

Nov 12 '05 #3
Thank you very much both of you. I will have a look at them now and
tell you how I got on
Mark
"Phil Stanton" <ph**@stantonfamily.co.uk> wrote in message news:<3f***********************@mercury.nildram.ne t>...
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

Nov 12 '05 #4
On 27 Nov 2003 06:54:26 -0800, ka*******@hotmail.com (Mark) wrote:
I have been trying to convert the week number to a range of dates that
I can use.


This is dead simple if you use a table instead of code. (And you
*should* use a table instead of code. Business data belongs in
tables.)

--
Mike Sherrill
Information Management Systems
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Richard Hollenbeck | last post: by
2 posts views Thread by David Mitchell | last post: by
3 posts views Thread by jerry.ranch | last post: by
3 posts views Thread by Manekurt | last post: by
3 posts views Thread by Tim Chase | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.