Finding the Beginning or End of a Week

Finding the first or last day in a week counts on the fact that you can subtract integers from a date value and end up with another date value. If the specified date was a Sunday, to find the first day of the week (assuming Sunday was the first day of the week), you’d subtract 0 from the date. If the date was a Monday, you’d subtract 1, if Tuesday, you’d subtract 2, and so on. Because the WeekDay function returns a number between 1 and 7, all you need to do is subtract the WeekDay return value from the date and then add 1. The dhFirstDayInWeek function, in Listing 2.2, does this work for you.

To be completely correct, the dhFirstDayInWeek and dhLastDayInWeek functions specify the first day of the week for the WeekDay function, using the vbUseSystem constant. This way, the first and last days in the week correspond to the local settings. If you were to omit this constant, the code would treat Sunday as the first day of the week and Saturday as the final day, no matter what the local settings.

The dhLastDayInWeek function in Listing 2.2 uses the same concepts. This time, however, you want to add 6 to the first day of the week. That is (assuming you’re in the United States), if the date in question is a Wednesday, you subtract the Weekday return value (4), which takes you to Saturday. Adding 1 takes you to the first day of the week, and adding 6 more takes you to the last day of the week.

Listing 2.2: Find the First or Last Day in a Week

Function dhFirstDayInWeek(Optional dtmDate As Date = 0) As Date
    ' Returns the first day in the week specified
    ' by the date in dtmDate.
    ' Uses localized settings for the first day of the week.
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    dhFirstDayInWeek = dtmDate - WeekDay(dtmDate, _
     vbUseSystem) + 1
End Function
Function dhLastDayInWeek(Optional dtmDate As Date = 0) As Date
    ' Returns the last day in the week specified by
    ' the date in dtmDate.
    ' Uses localized settings for the first day of the week.
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    dhLastDayInWeek = dtmDate - WeekDay(dtmDate, vbUseSystem) + 7
End Function

To call dhFirstDayInWeek and dhLastDayInWeek, pass a date value to specify a date, or pass no parameter to use the current date. For example, the following code calculates the first and last day in two different weeks:

Debug.Print "First day in the current week: " _
 & dhFirstDayInWeek()
Debug.Print "Last day in the current week: " & dhLastDayInWeek()
Debug.Print _
 "First day in the week of 1/1/98: " & dhFirstDayInWeek(#1/1/98#)
Debug.Print _
 "Last day in the week of 1/1/98: " & dhLastDayInWeek(#1/1/98#)