Dates and week numbers

[Alex]

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

[Salad]

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.

[Bruce M. Thompson]

> 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.<<

[Paradigm]

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.<<

[Bruce M. Thompson]

> 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.<<

[Pieter Linden]

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

[Mike Sherrill]

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