Date Functions

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.

On the CD 

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

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

Note 

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 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.

On the CD 

These functions are available on the companion CD-ROM, in a file named  extended date functions.xlsm. The CD also contains a Word file (extended data functions help.docx) that describes these functions.

Caution 

The extended date functions don't make any adjustments for changes made to the calendar in 1582. Consequently, working with dates prior to October 15, 1582, may not yield correct results.