Finding the date of the first day of the week using "ww" to ID Wee

[Guest]

Can you please advise what the formula would be to convert the week number
into the date of the first day of that week? I am running a report of daily
sales values and need to see each day within a week as well as sub-total
weeks and months. I know I can do some of this grouping in Report Writer, but
wnat to see a date for each week, not a week number for the year.

For example:
This is week 45. The first day was Sunday, first date of the week is
Nov-4-2007.

Kristi

 

[James A. Fortune]

Can you please advise what the formula would be to convert the week number
into the date of the first day of that week? I am running a report of daily
sales values and need to see each day within a week as well as sub-total
weeks and months. I know I can do some of this grouping in Report Writer, but
wnat to see a date for each week, not a week number for the year.

For example:
This is week 45. The first day was Sunday, first date of the week is
Nov-4-2007.

Kristi

Kristi,

Perhaps try (in VBA):

dtFirstDateOfWeek = DateAdd("d", (WeekNumber - 1) * 7, dtFirstDay)

or (in SQL):

SELECT DateAdd("d", ([WeekNumber] - 1) * 7, #12/31/06#) AS
FirstDateOfWeek FROM tblWithWeekNumbers WHERE [YearNumber] = 2007;

for your formula.

I suppose the WeekNumber and YearNumber combination can be used to group
the records into weeks. Do you calculate the WeekNumber's or are they
in the table? Also, do you group the months into normal months with
boundaries that are different from the week boundaries? If not, it
would be good for you to post an example of how you separate the weeks
into months. Maybe a better plan is needed. If I see how you want the
months grouped I can likely come up with a plan that will allow you to
produce the report you want.

James A. Fortune
(e-mail address removed)

 

[Pieter Wijnen]

This ought to do it

Public Function Week2Date(WeekNo As Long, Optional ByVal Yr As Long = 0, _
Optional ByVal DOW As VBA.VbDayOfWeek =
VBA.VbDayOfWeek.vbUseSystemDayOfWeek, _
Optional ByVal FWOY As VBA.VbFirstWeekOfYear =
VBA.VbFirstWeekOfYear.vbUseSystem) As Date
' Returns First Day of week
Dim Jan1 As Date
Dim Sub1 As Boolean
Dim Ret As Date

If Yr = 0 Then
Jan1 = VBA.DateSerial(VBA.Year(VBA.Date()), 1, 1)
Else
Jan1 = VBA.DateSerial(Yr, 1, 1)
End If
Sub1 = (VBA.Format(Jan1, "ww", DOW, FWOY) = 1)
Ret = VBA.DateAdd("ww", WeekNo + Sub1, Jan1)
Ret = Ret - VBA.Weekday(Ret, DOW) + 1
Week2Date = Ret
End Function

Pieter

 

[James A. Fortune]

This ought to do it

Public Function Week2Date(WeekNo As Long, Optional ByVal Yr As Long = 0, _
Optional ByVal DOW As VBA.VbDayOfWeek =
VBA.VbDayOfWeek.vbUseSystemDayOfWeek, _
Optional ByVal FWOY As VBA.VbFirstWeekOfYear =
VBA.VbFirstWeekOfYear.vbUseSystem) As Date
' Returns First Day of week
Dim Jan1 As Date
Dim Sub1 As Boolean
Dim Ret As Date

If Yr = 0 Then
Jan1 = VBA.DateSerial(VBA.Year(VBA.Date()), 1, 1)
Else
Jan1 = VBA.DateSerial(Yr, 1, 1)
End If
Sub1 = (VBA.Format(Jan1, "ww", DOW, FWOY) = 1)
Ret = VBA.DateAdd("ww", WeekNo + Sub1, Jan1)
Ret = Ret - VBA.Weekday(Ret, DOW) + 1
Week2Date = Ret
End Function

Pieter

Pieter,

I think it's good that you are trying to generalize the function for
various firstdayofweek and firstweekofyear values. Your
generalization/abstraction will cause a problem on some systems because
of the "ww" bug:

http://support.microsoft.com/kb/200299

entitled:

BUG: Format or DatePart Functions Can Return Wrong Week Number for Last
Monday in Year

I don't know if the bug has been fixed in A07 or not.

James A. Fortune
(e-mail address removed)