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

Dates and week numbers

P: n/a
Using the function datepart I can return a week number given any date.
What I now want to do is return a date (say monday) given a week
number. How can I do this?
Alex
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Alex wrote:
Using the function datepart I can return a week number given any date.
What I now want to do is return a date (say monday) given a week
number. How can I do this?
Alex


See if
Dim intInterval As Integer
intInterval = 2 '2nd week or the year
DateAdd("ww",intInterval,#1/1/2004#) + (vbMonday -
WeekDay(#1/1/2004#))
works.

You need to change vbMonday to whatever day or week you want for more
flexibility Basically this routine adds the weeks to the first of the
year and then points to the day in the week by subtracting the weekday of
the first of the year to whavever weekday you select.

Nov 12 '05 #2

P: n/a
> Using the function datepart I can return a week number given any date.
What I now want to do is return a date (say monday) given a week
number. How can I do this?


Try this "air code" function which should be copied to a standard module (a
global module, not a module behind a form or report):

'*********CODE START
Public Function GetDateByYearWeekNumWeekDay(iYear As Integer, _
iWeekNum As Integer, iWeekDayNum As Integer) As Date
Dim vdate As Date
vdate = DateSerial(iYear, 1, 1) + ((iWeekNum - 1) * 7)
GetDateByYearWeekNumWeekDay = DateValue(vdate - Weekday(vdate) + iWeekDayNum)
End Function
'*********CODE END

Usage:

MyDate = GetDateByYearWeekNumWeekDay(2004,12,vbThursday)

Which returns:

3/18/2004 (Thursday in the 12th week of this year)

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)
NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

Nov 12 '05 #3

P: n/a
Excellent. Works a treat. Thank you.
Alex

"Bruce M. Thompson" <bthmpson@big_NOSPAM_foot.com> wrote in message
news:vv************@corp.supernews.com...
Using the function datepart I can return a week number given any date.
What I now want to do is return a date (say monday) given a week
number. How can I do this?
Try this "air code" function which should be copied to a standard module

(a global module, not a module behind a form or report):

'*********CODE START
Public Function GetDateByYearWeekNumWeekDay(iYear As Integer, _
iWeekNum As Integer, iWeekDayNum As Integer) As Date
Dim vdate As Date
vdate = DateSerial(iYear, 1, 1) + ((iWeekNum - 1) * 7)
GetDateByYearWeekNumWeekDay = DateValue(vdate - Weekday(vdate) + iWeekDayNum) End Function
'*********CODE END

Usage:

MyDate = GetDateByYearWeekNumWeekDay(2004,12,vbThursday)

Which returns:

3/18/2004 (Thursday in the 12th week of this year)

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)
NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

Nov 12 '05 #4

P: n/a
> Excellent. Works a treat. Thank you.

You're quite welcome. Keep in mind the fact that it's "air code" and really
*should* have the benefit of some error-handling, but if you're careful in how
you pass the parameters to the function, you shouldn't have too many problems.

:-)
--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)
NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<
Nov 12 '05 #5

P: n/a
al********@hotmail.com (Alex) wrote in message news:<49**************************@posting.google. com>...
Using the function datepart I can return a week number given any date.
What I now want to do is return a date (say monday) given a week
number. How can I do this?
Alex


You could do something like
'air code... (insert air code siren sound...)

Function GetNextDate(dtInitialDate as date, intWeeks at integer,intDayNo as integer)

'intDayNo will correspond to vbMonday-vbSunday...
dim dtDate as date

dtDate = Dateadd("w",intWeeks,dtInitialDate)

do until weekday(dtDate)=intDayNo
dtDate=Dateadd("d",1,dtDate)
loop

GetNextDate =dtDate

End Function

Hope this gets you started.

Pieter
Nov 12 '05 #6

P: n/a
On 5 Jan 2004 11:18:45 -0800, al********@hotmail.com (Alex) wrote:
Using the function datepart I can return a week number given any date.
What I now want to do is return a date (say monday) given a week
number. How can I do this?


Did you consider something like this?

SELECT CalDate
FROM Calendar
WHERE (
(CalYear = 2003) AND
(CalDOW = 'Mon') AND
(ISOWeekNum = 8)
)

Implementation of the table Calendar is left as an exercise. ;)

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

This discussion thread is closed

Replies have been disabled for this discussion.