XL2000: How the DateSerial Function Works with Year Arguments

View products that this article applies to.

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.

Back to the top

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.

Back to the top

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 (http://support.microsoft.com/kb/214391/EN-US/ ) XL2000: How Microsoft Excel Works with Two-Digit Year Numbers

Back to the top

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.

Back to the top

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

Back to the top