Microsoft Office Tutorials and References
In Depth Information
NEXTMONDAY2 = d + 8 – WeekDay(d, vbMonday)
Calculating the next day of the week
The following NEXTDAY function is a variation on the NEXTMONDAY function. This function accepts two
arguments: A date and an integer between 1 and 7 that represents a day of the week (1 = Sunday, 2 = Monday,
and so on). The NEXTDAY function returns the date for the next specified day of the week.
Function NEXTDAY(d As Date, day As Integer) As Variant
Returns the next specified day
Make sure day is between 1 and 7
If day < 1 Or day > 7 Then
NEXTDAY = CVErr(xlErrNA)
NEXTDAY = d + 8 – WeekDay(d, day)
The NEXTDAY function uses an If statement to ensure that the day argument is valid (that is, between 1 and 7).
If the day argument is not valid, the function returns #N/A. Because the function can return a value other than a
date, it is declared as type Variant.
Which week of the month?
The following MONTHWEEK function returns an integer that corresponds to the week of the month for a date:
Function MONTHWEEK(d As Date) As Variant
‘ Returns the week of the month for a date
Dim FirstDay As Integer
‘ Check for valid date argument
If Not IsDate(d) Then
MONTHWEEK = CVErr(xlErrNA)
‘ Get first day of the month
FirstDay = WeekDay(DateSerial(Year(d), Month(d), 1))
‘ Calculate the week number
MONTHWEEK = Application.RoundUp((FirstDay + day(d) – 1) /
Working with dates before 1900