How the DateSerial Function Works with Year Arguments (183626)

MORE INFORMATION

The DateSerial function accepts three arguments: a year, a month, and a day. The year argument can be any value from 0 to 9999, inclusive. Depending on what version of Microsoft Excel you are using, the year argument is interpreted differently by Microsoft Excel. These differences are listed in the following table.

   Version of Microsoft Excel      Year argument   Interpreted as
   --------------------------------------------------------------

   Microsoft Excel 97 and          0-29            2000-2029
   Microsoft Excel 98              30-99           1930-1999
                                   100-9999        100-9999

   Microsoft Excel 7.x and         0-99            1900-1999
   Microsoft Excel 5.x             100-9999        100-9999

For example, assume you run a macro that contains the following line of code:

   MsgBox Format(SerialDate(29,1,15),"mm/dd/yyyy")

In Microsoft Excel 5.0 and 7.0, the message box displays the date as 1/15/1929. In Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition, the message box displays the date as 1/15/2029.

DateSerial Function versus Worksheet Dates

Note that this behavior is not entirely consistent with the behavior that is used by Microsoft Excel when you type a date into a cell by using only two digits for the year. For more information, please see the following article in the Microsoft Knowledge Base:

302768 How Microsoft Excel works with two-digit year numbers

DateSerial Function Help Topic Is Incorrect

The DateSerial function Help topic in the Visual Basic Reference in Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition contains the following text:

For the year argument, values between 0 and 99, inclusive, are interpreted as the years 1900-1999.

This is incorrect. The following information is correct:

For the year argument, values between 0 and 29, inclusive, are interpreted as the years 2000-2029. Values between 30 and 99, inclusive, are interpreted as the years 1930-1999.

Year "Wrapping" Caused by High Month or Day Arguments

If the month or day arguments that are specified in the DateSerial function are too high (for example, a month argument of 13), the year argument may be incremented to a higher value. This may cause a problem if the year argument is incremented so that it no longer is in one range of year arguments.

For example, if you run the following line of code

   MsgBox Format(DateSerial(99,13,20),"mm/dd/yyyy")

the date displayed in the message box is 1/20/100 not 1/20/2000 because this month argument (13) causes the year argument (99) to be incremented to 100.

Preventing Problems When You Use the DateSerial Function

To prevent problems from occurring when you create a macro that uses the DateSerial function and that may be run in multiple versions of Microsoft Excel, use four-digit-year numbers (for example, 1998) instead of two-digit-year numbers (for example, 98).