Year 2030 = 1930

D

D McAuliffe

I didn't think it would adversely affect me so soon after Y2K. I'm sorting
(ascending) GNMA issues which mature in 2032, and they listed before the
2000s. Both Excel 2000 and 602Software have the same cutoff date. I'll be
dead by 2030 so I'm not concerned about another Y2K fiasco; but does anyone
have work-a-rounds now? Thanks.
--
~~~~~~~~~~~~~~~~~
Dave McAuliffe
Central Mass. USA
To E-mail -
Replace: mailinator.com
With: email.com
~~~~~~~~~~~~~~~~~
 
T

Tony

These defaults are picked up from your computer's regional settings and are
not stored in Excel. The following shows you how to change the settings for
Windows XP.
1/ Start "Regional and Language Options" from the control panel
2/ On the "Regional Options" tab select customize...
3/ On the Date tab you can change the option on how to interpret two-digit
year (the default setting being set to 1930-2029

A better solution if possible is to always work with four-digit years.

hth
Tony
 
K

Ken Wright

Straight out of Chip Pearson's site from his section on Dates and Times:-

http://cpearson.com/excel/datetime.htm

The 29/30 cutoff is new to Excel97 and Excel2000. Excel95 uses 19/20 as the
cutoff year. If you have any doubts about how Excel will treat 2-digit years,
use the full 4-digit year. In Windows98 and Windows2000, you can specify the
cutoff year, from the Regional Setting control in the Windows Control Panel.

Note that the DATE worksheet function does not follow these rules. When you
enter a number less than 1900 for the year parameter of the DATE function,
DATE will add 1900 to that number. For example, =DATE(10,1,5) returns the date
5-Jan-1910, because DATE simply adds 10 to 1900 to compute the year.
Similarly, the formula =DATE(150,1,5) returns the date 2050-Jan-5, because
DATE adds 150 to the year 1900. DATE does not follow the same "cut off" rules
that cells do.
 
D

D McAuliffe

Ken Wright said:
Straight out of Chip Pearson's site from his section on Dates and Times:-

http://cpearson.com/excel/datetime.htm

The 29/30 cutoff is new to Excel97 and Excel2000. Excel95 uses 19/20 as the
cutoff year. If you have any doubts about how Excel will treat 2-digit years,
use the full 4-digit year. In Windows98 and Windows2000, you can specify the
cutoff year, from the Regional Setting control in the Windows Control Panel.

Note that the DATE worksheet function does not follow these rules. When you
enter a number less than 1900 for the year parameter of the DATE function,
DATE will add 1900 to that number. For example, =DATE(10,1,5) returns the date
5-Jan-1910, because DATE simply adds 10 to 1900 to compute the year.
Similarly, the formula =DATE(150,1,5) returns the date 2050-Jan-5, because
DATE adds 150 to the year 1900. DATE does not follow the same "cut off" rules
that cells do.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
Best wishes to all, and hope for a good New year :)
--------------------------------------------------------------------------
Click to expand...
--

Thanks to both you and Tony for the heads up.

--
~~~~~~~~~~~~~~~~~
Dave McAuliffe
Central Mass. USA
To E-mail -
Replace: mailinator.com
With: email.com
~~~~~~~~~~~~~~~~~
 
Top