XL: How the DateSerial Function Works with Year Arguments

ID: Q183626

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for the Power Macintosh, versions 5.0, 5.0a
Microsoft Excel 98 Macintosh Edition

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:

Q164406 XL: 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

Keywords : kbdta kbdtacode KbVBA
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0,7.0a; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type :