Find Dates For Sunday and Saturday

[Jerry]

How do I find the dates of the Sunday and Saturday of a week if I have the
week number of a selected year?

Thanks!

Jerry

[jimfortune]

If each week is exactly seven days starting January 1, then you can use
the following functions:
'-----------
Public Function NthXDay(N As Integer, D As Integer, dtD As Date) As
Integer
NthXDay = (6 - WeekDay(DateSerial(Year(dtD), Month(dtD), 1), 2) + D)
Mod 7 + 1 + (N - 1) * 7
End Function

Public Function GetSunday(intWeek As Integer, intYear As Integer) As
Variant
Dim intFirstSunday As Integer
Dim dtFirstSunday As Date
Dim dtSaturday As Variant

intFirstSunday = NthXDay(1, vbSunday, DateSerial(intYear, 1, 1))
dtFirstSunday = DateSerial(intYear, 1, intFirstSunday)
dtSaturday = GetSaturday(intWeek, intYear)
If Day(dtFirstSunday) = 1 Then
GetSunday = DateAdd("d", -6, dtSaturday)
Else
GetSunday = DateAdd("d", 1, dtSaturday)
End If
End Function

Public Function GetSaturday(intWeek As Integer, intYear As Integer) As
Variant
Dim intFirstSaturday As Integer
Dim dtFirstSaturday As Date

intFirstSaturday = NthXDay(1, vbSaturday, DateSerial(intYear, 1, 1))
dtFirstSaturday = DateSerial(intYear, 1, intFirstSaturday)
GetSaturday = DateAdd("ww", intWeek - 1, dtFirstSaturday)
End Function
'-----------

If you assume that each week begins on a Sunday (i.e., January 1, 2005
is the end of the first week) then change GetSunday to:
'-----------
Public Function GetSunday(intWeek As Integer, intYear As Integer) As
Variant
Dim dtSaturday As Variant

dtSaturday = GetSaturday(intWeek, intYear)
If Month(dtSaturday) = 1 And Day(dtSaturday) < 7 Then
GetSunday = Null
Else
GetSunday = DateAdd("ww", -1, dtSaturday)
End If
End Function
'-----------

Test to your satisfaction before using this code. Option 1 can use
Dates instead of Variants since there's no Null situation.
James A. Fortune

[jimfortune]

If each week is exactly seven days starting January 1, then you can use
the following functions:
'-----------
Public Function NthXDay(N As Integer, D As Integer, dtD As Date) As
Integer
NthXDay = (6 - WeekDay(DateSerial(Year(dtD), Month(dtD), 1), 2) + D)
Mod 7 + 1 + (N - 1) * 7
End Function

Public Function GetSunday(intWeek As Integer, intYear As Integer) As
Variant
Dim intFirstSunday As Integer
Dim dtFirstSunday As Date
Dim dtSaturday As Variant

intFirstSunday = NthXDay(1, vbSunday, DateSerial(intYear, 1, 1))
dtFirstSunday = DateSerial(intYear, 1, intFirstSunday)
dtSaturday = GetSaturday(intWeek, intYear)
If Day(dtFirstSunday) = 1 Then
GetSunday = DateAdd("d", -6, dtSaturday)
Else
GetSunday = DateAdd("d", 1, dtSaturday)
End If
End Function

Public Function GetSaturday(intWeek As Integer, intYear As Integer) As
Variant
Dim intFirstSaturday As Integer
Dim dtFirstSaturday As Date

intFirstSaturday = NthXDay(1, vbSaturday, DateSerial(intYear, 1, 1))
dtFirstSaturday = DateSerial(intYear, 1, intFirstSaturday)
GetSaturday = DateAdd("ww", intWeek - 1, dtFirstSaturday)
End Function
'-----------

If you assume that each week begins on a Sunday (i.e., January 1, 2005
is the end of the first week) then change GetSunday to:
'-----------
Public Function GetSunday(intWeek As Integer, intYear As Integer) As
Variant
Dim dtSaturday As Variant

dtSaturday = GetSaturday(intWeek, intYear)
If Month(dtSaturday) = 1 And Day(dtSaturday) < 7 Then
GetSunday = Null
Else
GetSunday = DateAdd("ww", -1, dtSaturday)
End If
End Function
'-----------

Test to your satisfaction before using this code. Option 1 can use
Dates instead of Variants since there's no Null situation.
James A. Fortune

[jimfortune]

Jerry wrote:

How do I find the dates of the Sunday and Saturday of a week if I have the week number of a selected year?

Thanks!

Jerry

Sorry about the multiple posts. Google was lying when they claimed a
communications error kept the reply from being posted.

James A. Fortune