Microsoft KB Archive/213511

= XL2000: How the DateSerial Function Works with Year Arguments =

Article ID: 213511

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213511





SUMMARY
When you use the DateSerial function in Visual Basic for Applications in Microsoft Excel, the date returned by the function may be different from one version of Microsoft Excel to the next. This article explains the differences in behavior.



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

Additional query words: 1929 1930 2029 2030 y2k XL2000

Keywords: kbdtacode kbhowto KB213511

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.