435,561 Members | 3,127 Online
Need help? Post your question and get tips & solutions from a community of 435,561 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
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" 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 weeknumber. 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.