Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Chapter 6 presents a number of useful Excel functions and formulas for calculating dates, times, and time periods by manipulating date and time serial values. This section presents additional functions that deal with dates.

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/31/2007, which is the first Monday after Christmas Day, 2007 (which is a Tuesday):

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

If the argument passed to the NEXTMONDAY function is a Monday, the function will return 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) = 2 Then NEXTMONDAY2 = d Else 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

Many users are surprised to discover that Excel can't work with dates prior to the year 1900. To correct this deficiency, I created a series of extended date functions. These functions enable you to work with dates in the years 0100 through 9999.

The extended date functions are

• XDATE(y,m,d,fmt): Returns a date for a given year, month, and day. As an option, you can provide a date formatting string.

• XDATEADD(xdate1,days,fmt): Adds a specified number of days to a date. As an option, you can provide a date formatting string.

• XDATEDIF(xdate1,xdate2): Returns the number of days between two dates.

• XDATEYEARDIF(xdate1,xdate2): Returns the number of full years between two dates (useful for calculating ages).

• XDATEYEAR(xdate1): Returns the year of a date.

• XDATEMONTH(xdate1): Returns the month of a date.

• XDATEDAY(xdate1): Returns the day of a date.

• XDATEDOW(xdate1): Returns the day of the week of a date (as an integer between 1 and 7).

Figure 25-5 shows a workbook that uses a few of these functions.

Figure 25-5: Examples of the extended date function.