By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 1,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Convert Week Number To Date Range

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a

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

P: n/a
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

P: n/a
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.