[AccessD] Figuring the date

Hi Drew, the DateSerial geek

Adding the count of days to the day parameter of DateSerial and not to the resulting date, it can be reduced to:

Function MondayDate(intYear As Long, intWeek As Long) As Date
  MondayDate = DateSerial(intYear, 1, 1 + 7 - Weekday(DateSerial(intYear, 1, 1), vbTuesday) + 7 * (intWeek - 1))
End Function

For everyone else: Note Drew's clever use of vbTuesday to turn Weekday into a kind of modulus function. If you would prefer vbMonday as the parameter in Weekday, you would have to use modulus or you would an additional week when Jan. 1 is the first day of the year:

  MondayDate = DateSerial(intYear, 1, (8 - Weekday(DateSerial(intYear, 1, 1), vbMonday)) Mod 7 + 1 + 7 * (intWeek - 1))

/gustav

>>> DWUTKA at marlow.com 06-03-2009 23:46 >>>
Function MondayDate(intYear As Long, intWeek As Long)
MondayDate = DateSerial(intYear, 1, 1 + (7 - Weekday(DateSerial(intYear,
1, 1), vbTuesday))) + (7 * (intWeek - 1))
End Function

Watch for word wrap there, the code inside that function should be all
one line.

It's the date of January 1st of whatever year, offset by when the first
Monday is, the 7-Weekday bit, plus 7*one less then the number of weeks.

So if you put MondayDate(2009,1), you'll get 1-5-2009, the first Monday
of the year.  MondayDate(2009,52) and you'll get 12-28-2009, the last
Monday of the year.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer
Gross
Sent: Friday, March 06, 2009 2:38 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Figuring the date

Okay, I have answers to my questions - they define Week 1 as the first
full week of the year and the week is defined as Monday to Sunday.  Any
thoughts on how to get that Monday date when I know the year and the
week #?

Jennifer

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer
Gross
Sent: Friday, March 06, 2009 12:34 PM
To: AccessD List
Subject: [AccessD] Figuring the date


Hey Everyone,

I have two pieces of information - the year and the week # within the
year, for instance 2008 13 is the 13th week in 2008.  I have questions
out to the client about how they define the week (Sun-Sat, Mon-Sun) and
how the first week of the year is defined - week that January 1 falls in
or first full week of the year.  Anyway - what I need to do is create a
function, unless one already exists, when fed these two pieces of
information returns the Monday date (short date format) for that week.  

Any help is appreciated.

Jennifer