Microsoft Office Tutorials and References

In Depth Information

NEXTMONDAY2 = d + 8 – WeekDay(d, vbMonday)

End If

End Function

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)

Else

NEXTDAY = d + 8 – WeekDay(d, day)

End If

End Function

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)

Exit Function

End If

‘ 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) /

7, 0)

End Function

Working with dates before 1900