Monday, August 1, 2011

Excel - Problem with Dates Older than 1930

If your dates are displaying incorrectly, e.g. 1935 shows as 2035, this is why. It can be frustrating because although the date looks correct in the cell, if you select the cell and view the formula bar you'll see a different date shows there. This occurs only when you use 2 digits for the year. You will not have this problem if you use 4 digits for the year.

Any 2-digit year on or after 1/1/30 (1/1/1930) will show correctly in the 1900's. Any 2-digit year before 1/1/30 will show incorrectly in the 2000's, basically putting a '20' in front of the 2 digits instead of '19'.  Thus:

1/1/30 = 1/1/1930
1/1/31 = 1/1/1931


1/1/00 = 1/1/2000
1/1/01 = 1/1/2001
1/1/29 = 1/1/2029

I recommend always using 4-digit years. You can then format the cell to display anyway you'd like (use "customize" if the way you like is not shown).

Microsoft support offers these solutions:

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...