XL2000: How the DateSerial Function Works with Year Arguments (213511)

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:

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