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.

Date Functions

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

Monday:

Function NEXTMONDAY(d As Date) As Date

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

End Function

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

Wednesday):

=NEXTMONDAY(DATE(2013,12,25))

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

Else