Microsoft KB Archive/183626

= How the DateSerial Function Works with Year Arguments =

Article ID: 183626

Article Last Modified on 10/10/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q183626





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

Additional query words: XL98 XL97 XL5 XL7 5.0 7.0 1929 1930 2029 2030 year2000 y2k year 2000 XL

Keywords: kbdtacode KB183626

-

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

© Microsoft Corporation. All rights reserved.