Microsoft KB Archive/214068

= XL: Date Values Earlier Than 1900 Appear as Text =

Article ID: 214068

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q214068



For a Microsoft Excel for Macintosh version of this article, see 323218.



SUMMARY
Microsoft Excel calculates date serial numbers starting with the year 1900 and later. When you use a date that is earlier than the year 1900, the date value appears as a text string. This same behavior occurs in Microsoft Excel for the Macintosh when you use dates earlier than 1904.

NOTE: Excel for the Macintosh uses a base date of January 1, 1904.

One way to determine if a date is formatted as a text string is by looking at the alignment of the value in the cell:


 * The default format for dates and values is right-aligned.
 * The default format for text strings is left-aligned.



MORE INFORMATION
If a date is formatted as a text value, you cannot use it in certain calculations. For example, you cannot easily find out the number of years between two dates if one of the dates is earlier than the year 1900.

The following example describes a formula to find the number of years between two dates when one of the dates is earlier than the year 1900.

Example
If cell A1 of your worksheet contains the date 1/1/1865, and you want to calculate the elapsed years between the date in cell A1 and today's date, use the following formula:

=IF(ISTEXT(A1),YEAR(TODAY)-RIGHT(A1,4),(TODAY-A1)/365)

If the current year is 1999, this formula returns 134, which indicates that the difference in years between the two dates is 134 years.

The following is an explanation of this formula:

The ISTEXT function performs a logical test as to whether the string found in A1 is text or not.

The YEAR function is used in this case to convert the date returned by the TODAY function to the actual year (that is, it evaluates 1/1/1999 to 1999).

The RIGHT function is used to extract only the last four values of the string.

Additional query words: xl2000 xl2002 xl97

Keywords: kbhowto KB214068

-

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

© Microsoft Corporation. All rights reserved.