[AccessD] HOW TO: Determine start and end of week given a specific date

Here is some real old stuff that I got from somewhere. I'm sure someone will
find a flaw in it someplace.


Function StartOfWeek(ByVal D As Date) As Date
    StartOfWeek = D - WeekDay(D) + 1
End Function

Function EndOfWeek(ByVal D As Date) As Date
     EndOfWeek = D - WeekDay(D) + 7
End Function

Function EndOfMonth(ByVal D As Date) As Date
     EndOfMonth = DateSerial(Year(D), Month(D) + 1, 0)
End Function

Function StartOfMonth(ByVal D As Date) As Date
    StartOfMonth = DateSerial(Year(D), Month(D), 1)
End Function


There ya' go. Hope they work.

John Skolits

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Thursday, January 30, 2003 1:14 PM
To: Seth Galitzer
Subject: Re: [AccessD] HOW TO: Determine start and end of week given a
specific date


Hi Seth

That looks nice, Seth, but as you know it isn't of much value on this
side of the ocean where the first day of the week is Monday according
to the ISO standard.

I have two generic functions for this purpose which tries to follow
the Access conventions for dealing with dates and day values and using
the built in constants where possible. They should fit for any
environment (I won't use that in..-word to spare JC who is busy for
moment).

The default value for the weekstart is set to Monday for various
reasons but could easily be changed to vbUseSystemDayOfWeek.

It doesn't turn out as simple as yours (and Charlotte's) but, anyway,
here goes:

<code>

Function DateWeekFirst( _
  ByVal datDate As Date, _
  Optional ByVal lngFirstDayOfWeek As Long = vbMonday) _
  As Date

' Returns the first date of the week of datDate.
' lngFirstDayOfWeek defines the first weekday of the week.
' 2000-09-07. Cactus Data ApS.

  ' No special error handling.
  On Error Resume Next

  ' Validate lngFirstDayOfWeek.
  Select Case lngFirstDayOfWeek
    Case _
      vbMonday, _
      vbTuesday, _
      vbWednesday, _
      vbThursday, _
      vbFriday, _
      vbSaturday, _
      vbSunday, _
      vbUseSystemDayOfWeek
    Case Else
      lngFirstDayOfWeek = vbMonday
  End Select

  DateWeekFirst = DateAdd("d", vbSunday - _
    WeekDay(datDate, lngFirstDayOfWeek), datDate)

End Function

Function DateWeekLast( _
  ByVal datDate As Date, _
  Optional ByVal lngFirstDayOfWeek As Long = vbMonday) _
  As Date

' Returns the last date of the week of datDate.
' lngFirstDayOfWeek defines the first weekday of the week.
' 2000-09-07. Cactus Data ApS.

  ' No special error handling.
  On Error Resume Next

  ' Validate lngFirstDayOfWeek.
  Select Case lngFirstDayOfWeek
    Case _
      vbMonday, _
      vbTuesday, _
      vbWednesday, _
      vbThursday, _
      vbFriday, _
      vbSaturday, _
      vbSunday, _
      vbUseSystemDayOfWeek
    Case Else
      lngFirstDayOfWeek = vbMonday
  End Select

  DateWeekLast = DateAdd("d", vbSaturday - _
    WeekDay(datDate, lngFirstDayOfWeek), datDate)

End Function

</code>

/gustav


> Just thought I'd share something I worked out just now, only 'cause I
> thought it was spiffy and was sure newbies and oldies might find a use
> for it.

> So a user comes up to me and asks me to make a report that they would
> need to generate from time to time which would contain a week's-worth of
> data.  However, they might not always generate it on time and so would
> need to be able to get data from any given week.  "No problem!" I told
> them.  And it turns out I was right.

> I'll leave the interface up to you, because the spiffy part is getting
> the first and last day of the week.  We always wanted to get data from a
> given week, starting with Sunday and ending with Saturday (standard
> Western week period).  The solution lies in a little built-in function
> called Weekday().  This function returns the ordinal value of the day of
> the week given that day as a Date value.  So Weekday(#1/29/2003#) would
> return 4. With a bit of simple math and the use of the DateAdd()
> function, we can get either the start date or end date of a week given
> any date with one line of code each.

> Here are the two functions I wrote (error-hendler left out):

> Public Function basGetWeekStart(dte As Date) As Date
>     'Subtract the ordinal value of dte and add 1 to get the Sunday
>     '   before dte
>     basGetWeekStart = DateAdd("d", -(Weekday(dte)) + 1, dte)
> End Function

> Public Function basGetWeekEnd(dte As Date) As Date
>     'Subtract the ordinal value of dte from 7 (vbSaturday = 7)
>     '   and add that many days to dte to get the Saturday after dte
>     basGetWeekEnd = DateAdd("d", vbSaturday - (Weekday(dte)), dte)
> End Function

> Note that by default, Weekday() uses Sunday as the start of the week,
> but that can be overriden to suit your needs by adding one more
> parameter value to the function call.  See Help on the function for
> constant values to use.

> This is the kind of code I like: simple, elegant, and relatively
> self-explanitory.

> Enjoy!

> Seth

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com