Weekday Date based on Week Number

[Joshua]

Hello!

This has to be simple, but I can't figure it out. I'm simply trying to
return the Monday date based off of week number.

SO, for example ...

Week number 43 in 2008: Monday date is 10/20/08.

I'm updating a field in a table using an update query btw.

Thanks for your help!

 

[Эйдельман]

Hello!

This has to be simple, but I can't figure it out. I'm simply trying to
return the Monday date based off of week number.

SO, for example ...

Week number 43 in 2008: Monday date is 10/20/08.

I'm updating a field in a table using an update query btw.

Thanks for your help!

 

[OssieMac]

Hi Joshua,

The following test sub should give you the idea.

Sub WeekDay_Test()

Dim dateYearStart As Date
Dim dateWeekNumb As Date
Dim dateMonday as Date
Dim i As Integer

dateYearStart = #1/1/2008#

For i = 1 To 52
'Calculate a date within the week number
dateWeekNumb = dateYearStart + (i - 1) * 7

'Calculate the date of Monday within the week number
dateMonday = dateWeekNumb - Weekday(dateWeekNumb, _
vbMonday) + 1

'Display the date with the day of week
MsgBox Format(dateMonday, "ddd dd mmm yyyy")

Next i

End Sub

 

[John Spencer]

It kind of depends on how you define when week 1 starts. The following
expression assumes that the first week is the first full week of the year.

DateAdd("ww",1,DateAdd("d",1-Weekday(#1/1/2009#),#1/1/2009#))

If you want the first week to be the week where the 1 of January occurs then
you MIGHT be able to adjust the formula by subtracting 1 from the week number.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[OssieMac]

Hi again Joshua,

Afterthought. If using the Weekday function in a query you will need the
numeric equivalent of vbMonday which is 2. (vbSunday = 1 to vbSaturday = 7)

 

[Joshua]

Thank you for the multiple responses. They were very helpful and I got it
working!