Week number to weekending date

[sdawson]

I use the module below to return the week number (ISOWeekNum) for a date input in usual format (dd/mm/yy).

I now need to return the week ending date (Sunday) from an input week number in a query. That is, I input the week number and the query filters the data up to the week ending date for the week number input. Got it.

This should be simple.
Any links or help appreciated.

Ta

-----------------------------------------------------------------------

Option Compare Database
Option Explicit

Public Function ISOWeekNum(AnyDate As Date, _
Optional WhichFormat As Variant) As Integer


' WhichFormat: missing or <> 2 then returns week number,
' = 2 then YYWW
'WhichFormat = 2


Dim ThisYear As Integer
Dim PreviousYearStart As Date
Dim ThisYearStart As Date
Dim NextYearStart As Date
Dim YearNum As Integer

ThisYear = Year(AnyDate)
ThisYearStart = YearStart(ThisYear)
PreviousYearStart = YearStart(ThisYear - 1)
NextYearStart = YearStart(ThisYear + 1)
Select Case AnyDate
Case Is >= NextYearStart
ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
YearNum = Year(AnyDate) + 1
Case Is < ThisYearStart
ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1
YearNum = Year(AnyDate) - 1
Case Else
ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
YearNum = Year(AnyDate)
End Select


If IsMissing(WhichFormat) Then
Exit Function
End If
If WhichFormat = 2 Then
ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _
Format(ISOWeekNum, "00"))

End If

End Function

------------------------------------------------------------------------

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
If WeekDay < 4 Then
YearStart = NewYear - WeekDay
Else
YearStart = NewYear - WeekDay + 7
End If
End Function

 

[Simon_MT]

May I suugest that if you put a Dailogue form in front with two combis Start Week and End Week you could table drive the whole process using a Week Master table containing Week Start Date and End Date.

I know this is a very simplistic view but I needed to allow users to produce date based reports using the same dates so instead of having week I have Start Date and End Date along with a combi from Sales Reports so that having selected the reporting period they can run as many reports as they require.

All the reports underlying queries use the Srart Date and End date fron my Dialog form and Hide the form on Report open and re-open the form when each report is closed.

Simon

 

[sdawson]

To create what you have would be a bit long winded to incorporate into an existing db and week numbers are our normal way of working.
Thanks for the input though.

 

[Simon_MT]

I agree but it took me no time at all and kicked myself for not thinking of it in the first place.

Simon

 

[raskew]

Hi -

Give this a try:

Public Function fWkNumToDate(pWkNum As Integer, pYear As Integer) As String
'Purpose:   Return the start and end dates of a week
'           based on the year and week number (based on
'           the first full week of the year, starting on
'           a Sunday).
'Coded by:  raskew
'Inputs:    1) ? fWkNumToDate(1, 2008)
'           2) ? fWkNumToDate(32, 2008)

'Outputs:   1) 1/6/2008 - 1/12/2008
'           2) 8/10/2008 - 8/16/2008

Dim dteStart As Date
Dim dteHold  As Date
Dim intHold  As Integer
 
   dteHold = DateSerial(pYear, 1, 1)
   intHold = WeekDay(dteHold)
   dteStart = IIf(intHold <> 1, dteHold - intHold + 8, dteHold)
   
   fWkNumToDate = dteStart + (7 * ((pWkNum) - 1)) & " - " & dteStart + (7 * (pWkNum) - 1)

End Function

Since you're using weeks starting on Monday, change this line:

dteStart = IIf(intHold <> 1, dteHold - intHold + 8, dteHold)

to:

dteStart = IIf(intHold <> 2, dteHold - intHold + 8, dteHold)

HTH - Bob