Given a week Number, how do I calculate the Monday of that week?

[Vince]

Given a week Number, how do I calculate the date that for the Monday
of that week?

[Tom van Stiphout]

On Thu, 9 Oct 2008 00:53:32 -0700 (PDT), Vince <Vi*******@gmail.com>
wrote:

Use the DatePart function with "w" to get the weekday of the given
date. Then use DateAdd to subtract a few days to arrive at Monday,
which is day 2 if you don't specify a FirstDayOfWeek argument.

-Tom.
Microsoft Access MVP

>Given a week Number, how do I calculate the date that for the Monday
of that week?

[Salad]

Vince wrote:

Given a week Number, how do I calculate the date that for the Monday
of that week?

You could copy this code into a module and play around with it. Change
intWeekNum in GetTheMonday for the number of weeks to add to test. You
can see this year's (2008) first Monday week is before the first of the
year so make adjustments as required.

Run GetTheMonday to cycle thru 2006-2008. Check results in Immediate
window.

Private Sub GetTheMonday
Dim datMonday As Date
Dim intFor as Integer
Dim intWeekNum As Integer

intWeekNum = 1 'week number to calc date from

'show in debug window the first monday for years 2006-2008
For intFor = 2006 to 2008
'get date of first monday in year 2008
Debug.Print MondayWeek(intFor,intWeekNum)
Next

endif

Public Function MondayWeek(intYear As integer, _
intWeek As Integer) As Date
Dim dat As Date
Dim intD As Integer

'calc first day of year of datFld
dat = DateSerial(intYear, 1, 1)
intD = WeekDay(dat)
Select Case intD
Case 1
dat = dat + 1
Case Else
'subtract 2 to get first Monday
dat = dat - (intD - 2)
End Select
'if datYear is
'2006 1/2/2006 is first monday
'2007 1/1/2007 is first monday
'2008 12/31/2007 is first monday

'add number of weeks minus 1 to first monday
MondayWeek = DateAdd("ww", intWeek - 1, dat)
End Function

Gronlandic Edit
http://www.youtube.com/watch?v=HBfgQvM7wtE

[Vince]

On Oct 9, 7:34*am, Salad <[e-mail]:

Vince wrote:

Given a week Number, how do I calculate the date that for theMonday
of that week?

You could copy this code into a module and play around with it. *Change
intWeekNum in GetTheMonday for the number of weeks to add to test. *You
can see this year's (2008) firstMondayweek is before the first of the
year so make adjustments as required.

Run GetTheMonday to cycle thru 2006-2008. *Check results in Immediate
window.

Private Sub GetTheMonday
* * * * Dim datMonday As Date
* * * * Dim intFor as Integer
* * * * Dim intWeekNum As Integer

* * * * intWeekNum = 1 *'week number to calc date from

* * * * *'show in debug window the firstmondayfor years 2006-2008
* * * * For intFor = 2006 to 2008
* * * * * 'get date of firstmondayin year 2008
* * * * * Debug.Print MondayWeek(intFor,intWeekNum)
* * * * Next

endif

Public Function MondayWeek(intYear As integer, _
* * * * intWeek As Integer) As Date
* * *Dim dat As Date
* * *Dim intD As Integer

* * *'calc first day of year of datFld
* * *dat = DateSerial(intYear, 1, 1)
* * *intD = WeekDay(dat)
* * *Select Case intD
* * *Case 1
* * * * *dat = dat + 1
* * *Case Else
* * * * 'subtract 2 to get firstMonday
* * * * *dat = dat - (intD - 2)
* * *End Select
* * *'if datYear is
* * * * *'2006 1/2/2006 is firstmonday
* * * * *'2007 1/1/2007 is firstmonday
* * * * *'2008 12/31/2007 is firstmonday

* * *'add number of weeks minus 1 to firstmonday
* * *MondayWeek = DateAdd("ww", intWeek - 1, dat)
End Function

Gronlandic Edithttp://www.youtube.com/watch?v=HBfgQvM7wtE

thanks !! the Function MondayWeek worked for me!

[Salad]

Vince wrote:

On Oct 9, 7:34 am, Salad <[e-mail]:

>>Vince wrote:

>>>Given a week Number, how do I calculate the date that for theMonday
of that week?

You could copy this code into a module and play around with it. Change
intWeekNum in GetTheMonday for the number of weeks to add to test. You
can see this year's (2008) firstMondayweek is before the first of the
year so make adjustments as required.

Run GetTheMonday to cycle thru 2006-2008. Check results in Immediate
window.

Private Sub GetTheMonday
Dim datMonday As Date
Dim intFor as Integer
Dim intWeekNum As Integer

intWeekNum = 1 'week number to calc date from

'show in debug window the firstmondayfor years 2006-2008
For intFor = 2006 to 2008
'get date of firstmondayin year 2008
Debug.Print MondayWeek(intFor,intWeekNum)
Next

endif

Public Function MondayWeek(intYear As integer, _
intWeek As Integer) As Date
Dim dat As Date
Dim intD As Integer

'calc first day of year of datFld
dat = DateSerial(intYear, 1, 1)
intD = WeekDay(dat)
Select Case intD
Case 1
dat = dat + 1
Case Else
'subtract 2 to get firstMonday
dat = dat - (intD - 2)
End Select
'if datYear is
'2006 1/2/2006 is firstmonday
'2007 1/1/2007 is firstmonday
'2008 12/31/2007 is firstmonday

'add number of weeks minus 1 to firstmonday
MondayWeek = DateAdd("ww", intWeek - 1, dat)
End Function

Gronlandic Edithttp://www.youtube.com/watch?v=HBfgQvM7wtE

thanks !! the Function MondayWeek worked for me!

You're welcome. I'm still a bit concerned about a Monday falling into
the previous year but I suppose you could check if the Monday is in a
prior year (if it shouldn't be, although maybe it should) and add 7 to
it. Ex:
MondayWeek = DateAdd("ww", intWeek - 1, dat)
'next line for testing if you need to increment by 7
If Year(dat) < intYear then MondayWeek = MondayWeek + 7
If you don't want the first Monday to fall in a prior year and do add 7
you might want to modify the code in GetTheMonday cycle through 1900 to
2008 to see if by adding 7 you end up in the next year by doing a
Debug.Print MondayWeek if you need/want to add 7 days. I might also
want to check this for week 52.

intWeekNum = 1 'week number to calc date from
For intFor = 1900 to 2008
and
intWeekNum = 52 'week number to calc date from
For intFor = 1900 to 2008
then in function MondayWeek
'print the years
MondayWeek = DateAdd("ww", intWeek - 1, dat)
If Year(dat) <intYear then Debug.print intYear;MondayWeek
You might find some anomolies in cycling thru all those years.