Given a week Number, how do I calculate the date that for the Monday
of that week?
of that week?
-
-
Re: Given a week Number, how do I calculate the Monday of that week?
On Thu, 9 Oct 2008 00:53:32 -0700 (PDT), Vince <VinceRuso@gmai l.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? -
Re: Given a week Number, how do I calculate the Monday of that week?
Vince wrote:You could copy this code into a module and play around with it. ChangeGiven a week Number, how do I calculate the date that for the Monday
of that week?
>
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(intF or,intWeekNum)
Next
endif
Public Function MondayWeek(intY ear As integer, _
intWeek As Integer) As Date
Dim dat As Date
Dim intD As Integer
'calc first day of year of datFld
dat = DateSerial(intY ear, 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
Comment
-
Re: Given a week Number, how do I calculate the Monday of that week?
On Oct 9, 7:34 am, Salad <o...@vinegar.c omwrote:thanks !! the Function MondayWeek worked for me!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(intF or,intWeekNum)
Next
>
endif
>
Public Function MondayWeek(intY ear As integer, _
intWeek As Integer) As Date
Dim dat As Date
Dim intD As Integer
>
'calc first day of year of datFld
dat = DateSerial(intY ear, 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=HBfgQvM 7wtE
Comment
-
Re: Given a week Number, how do I calculate the Monday of that week?
Vince wrote:
You're welcome. I'm still a bit concerned about a Monday falling intoOn Oct 9, 7:34 am, Salad <o...@vinegar.c omwrote:
>>>>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(intF or,intWeekNum)
> Next
>>
>>endif
>>
>>Public Function MondayWeek(intY ear As integer, _
> intWeek As Integer) As Date
> Dim dat As Date
> Dim intD As Integer
>>
> 'calc first day of year of datFld
> dat = DateSerial(intY ear, 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=HBfgQvM 7wtE
>
thanks !! the Function MondayWeek worked for me!
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;MondayW eek
You might find some anomolies in cycling thru all those years.
Comment
Comment