Finding a start day and end day of a giving week

[Funkyaccess]

I have a report which formats a value for me but its not doing quiet what I need it to.

This is its control source:

=format$([tblColumn],'ww',0,0)

Which return for eg. 41

I need to return the Mondays and Fridays date of week 41.

Is there another way I can change the weeks to work either with financial years or annual years.

 

[Mr. B]

Here are two public functions.

The function below will return the starting date of the week (day 1, Sunday) of the week based on the date passed to it.

Public Function GetWeekStartDate(dtProvidedDate As Date) As Date
Dim bytDayOfDate As Byte
Dim bytDayCnt As Byte
bytDayOfDate = DatePart("w", dtProvidedDate)
'start day of week is 1
bytDayCnt = bytDayOfDate - 1
'return the starting day of the week
GetWeekStartDate = DateAdd("d", -bytDayCnt, dtProvidedDate)
End Function

You would call this function with the following line:

A Variable or a control name = GetWeekStartDate(provide a date here)

For example, if you had a form with a text box named: "txtProvidedDate" where you could type in a date, and you had another text box named: "txtWkStart" then you could call the function like:

Me.txtWkStart = GetWeekStartDate(Me.txtProvidedDate)

The function below will return the ending date of the week (day 7, Saturday) of the week based on the date passed to it.

Public Function GetWeenEndDate(dtProvidedDate As Date) As Date
Dim bytDayOfDate As Byte
Dim bytDayCnt As Byte
bytDayOfDate = DatePart("w", dtProvidedDate)
'end day of week is 7
bytDayCnt = 7 - bytDayOfDate
'return the ending day of the week
GetWeenEndDate = DateAdd("d", bytDayCnt, dtProvidedDate)
End Function

This function is call exactly like the "GetWeekStartDate" function.

 

[raskew]

Hi -

Try playing with this

Public Function fGetDates(pwknum As Integer) As String
'************************************************
'Purpose:   Return a 'between' statement
'           based on the Monday and Friday
'           of an input week number
'Coded by:  raskew
'Inputs:    ? fgetdates(41)
'Output:    between #10/5/2009# and #10/9/2009#
'Note:      Changing the [COLOR="Red"]date in x[/COLOR] would allow you
'           to input a FY start date rather than 1 Jan
'************************************************
Dim w As Integer
Dim x As Date
Dim startdte As Date
Dim enddte As Date

   w = pwknum
   x = DateAdd("ww", w - 1, [COLOR="Red"]DateSerial(year(Date), 1, 1)[/COLOR])
   startdte = x - WeekDay(x) + 2
   enddte = x - WeekDay(x) + 6

   [B]fGetDates = "between #" & startdte & "# and #" & enddte & "#"[/B]

End Function

HTH - Bob

 

[Funkyaccess]

Many thanks both I've now got it working using your examples.

ANy ideas how I could find the same for a quarter?

ANd nother question is there a way to start the weeknumbers based on Financial year rather than Annual year and the same with quarter

 

[raskew]

Hi -

Take a look at the note in the function I provided:

'Note: Changing the date in x would allow you
' to input a FY start date rather than 1 Jan

Most likely replacing references to 'w' with 'q' would be a start with working with quarters.

Bob

 

[smig]

if [tblColumn] is a date (that's how it looks like) you can use:

dayinweek = format([tblColumn], "w") will give the week day
daystomonday = 2 - dayinweek
daystofriday = 6 - dayinweek

Monday = dateadd("d", [tblColumn], daystomonday )
Friday = dateadd("d", [tblColumn], daystofriday )