The
DateSerial function accepts three arguments: a year, a month, and a day. The year argument can be any value from 0 to 9,999, inclusive.
The year argument is interpreted differently by earlier versions of Microsoft Excel. These differences are listed in the following table.
Version of Microsoft Excel Year argument Interpreted as
--------------------------------------------------------------
Microsoft Excel 2000 and 0-29 2000-2029
Microsoft Excel 97 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 2000, the message box displays the date as 1/15/2029. In Microsoft Excel 5.0 and 7.0, the message box displays the date as 1/15/1929.
DateSerial Function and Worksheet Dates
NOTE: 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 additional information about using two-digit year numbers in Excel, click the article number below
to view the article in the Microsoft Knowledge Base:
214391�
XL2000: How Microsoft Excel Works with Two-Digit Year Numbers
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, 1999) instead of
two-digit year numbers (for example, 99).