Microsoft Office Tutorials and References
In Depth Information
ber of spaces are counted and added to the total. Then one more space is added because a sentence with three
spaces has four words. Spaces are counted by comparing the length of the text string with the length after the
spaces have been removed with the VBA Replace function.
Chapter 6 presents a number of useful Excel functions and formulas for calculating dates, times, and time peri-
ods by manipulating date and time serial values. This section presents additional functions that deal with dates.
This topic's website contains a workbook, date functions.xlsm, that demonstrates the
functions presented in this section.
Calculating the next monday
The following NEXTMONDAY function accepts a date argument and returns the date of the following
Function NEXTMONDAY(d As Date) As Date
NEXTMONDAY = d + 8 – WeekDay(d, vbMonday)
This function uses the VBA WeekDay function, which returns an integer that represents the day of the week for
a date (1 = Sunday, 2 = Monday, and so on). It also uses a predefined constant, vbMonday.
The following formula returns 12/30/2013 , which is the first Monday after Christmas Day, 2013 (which is a
The function returns a date serial number. You will need to change the number format
of the cell to display this serial number as an actual date.
If the argument passed to the NEXTMONDAY function is a Monday, the function returns the following
Monday. If you prefer the function to return the same Monday, use this modified version:
Function NEXTMONDAY2(d As Date) As Date
If WeekDay(d) = vbMonday Then
NEXTMONDAY2 = d