[AccessD] Figuring the date

A further small optimization:

Replace ... 1+7 ..............+7*(intWeek - 1)
with       .....1+................. +7*(intWeek)

i.e.
DateSerial(intYear, 1, 1 - Weekday(DateSerial(intYear, 1, 1), vbTuesday)) + 7 * (intWeek)


-- 
Stuart

On 7 Mar 2009 at 11:11, Gustav Brock wrote:

> 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
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com