[AccessD] Figuring the date

Jennifer,
 Here are 2 functions that work together. I found them on google.
When testing it I found it didn't handle the 1st week of Year  where the
company's year starts on first full week. I added the If condition in
WeekStart.

Hope it helps.
Thanks to John Green, an Excel MVP from Australia who did original thinking.

Jack

*****************************************************************

Public Function YearStart(WhichYear As Integer) As Date
Dim WeekDay As Integer
Dim NewYear As Date
NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0
If WeekDay < 4 Then
    YearStart = NewYear - WeekDay
Else
    YearStart = NewYear - WeekDay + 7
End If

End Function


'Monday Of A Given Week Number (WeekStart)
'========================================
'This function returns the date of the Monday in a given week number,
'eg: the Monday of the 1st week of 2009. It calls the function YearStart.
' WeekStart(1,2009)
Public Function WeekStart(WhichWeek As Integer, WhichYear As _
                    Integer) As Date

WeekStart = YearStart(WhichYear) + ((WhichWeek - 1) * 7)

If Year(WeekStart) < WhichYear Then
 WeekStart = YearStart(WhichYear) + (WhichWeek * 7)
Else
'
'this is required since Company Year starts on FirstFullWeek
 WeekStart = YearStart(WhichYear) + ((WhichWeek - 1) * 7)
End If

End Function

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer Gross
Sent: Friday, March 06, 2009 3: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
No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.0.237 / Virus Database: 270.11.8/1986 - Release Date:
03/05/09 19:32:00


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com