Find next year when date on specific weekday

[Guest]

I need a way to figure out in what year a date will be on a specific weekday
(e.g., when will Dec. 25 be on a Sunday), short of looking through years and
years of a calendar. Is there a formula or multiple formulas I can use to
figure this out in Access or Excel? I know there are ways I can manually
calculate it based on calendar shifts, but I'm looking for something
tool-based.

 

[Tom Wickerath]

Here is a function with a hard-coded weekday (vbSunday) that might work for you:


Function DetermineYear(strMonthDay As String) As Date
On Error GoTo ProcError

' Input: Month/Day as string
' Determines the next date that a given Month/Day input will occur on the desired day
' Example in Immediate Window: ?DetermineYear("12/25")
' Returns: 12/25/2005

Dim dteDate As Date
dteDate = CDate(strMonthDay & "/" & Year(Now()))

Do Until Weekday(dteDate) = vbSunday
dteDate = DateAdd("yyyy", 1, dteDate)
Loop

DetermineYear = dteDate


ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbInformation, "Error in DetermineYear event procedure..."
Resume ExitProc
End Function



Tom
_________________________________________


I need a way to figure out in what year a date will be on a specific weekday
(e.g., when will Dec. 25 be on a Sunday), short of looking through years and
years of a calendar. Is there a formula or multiple formulas I can use to
figure this out in Access or Excel? I know there are ways I can manually
calculate it based on calendar shifts, but I'm looking for something
tool-based.