Excel: Date Values Earlier Than 1900 Appear As Text

ID: Q104226


The information in this article applies to:


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 will appear as a text string. This same behavior occurs in Microsoft Excel for the Macintosh when you use dates earlier than 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:


MORE INFORMATION

If a date is formatted as a text value, you will not be able to use it in certain calculations. For example, you could not easily find out the number of years between two dates if one of the dates is earlier than the year 1900.

The example below describes a formula to find the number of years between two dates when one of the dates is prior to 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 1993, the formula above would return 128, indicating that the difference in years between the two dates is 128 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/1993 to 1993).

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


REFERENCES

"Function Reference," version 4.0, pages 225-228, 468, 435, 363
"Function Reference," version 3.0, page 125-127, 251, 237, 200

Additional query words: 2.x 4.00a


Keywords          : 
Version           : WINDOWS:3.0,4.0,5.0,7.0,97; MACINTOSH:3.0,4.0,5.0
Platform          : MACINTOSH WINDOWS 
Issue type        : 

Last Reviewed: April 6, 1999