wrong year

[Dwight]

Hi all,

This problem started today. Iam using Excel 2003. I format my dates to
display as dd-mmm-yyyy. No problem until today. When I import a delimted
file, I select the dmy format. No problem. But, when I format the column to
display the dates as dd-mmm-yyyy, some of the dates are in the wrong
century. The dates that I deal with are either in the 1800's or 1900's and
not in the 2000's. Only the dates in the 1900's are affected, but not all.
They are correctly displayed in the formula bar, but after I format them
some are displayed as 02-May-2014 instead of 02-May-1914. This is the first
time I have had this happen. What gives?tks

dwight

 

[Gord Dibben]

Dwight

Might help if you go into Regional Settings in Control Panel and change the
ending year for the century under Customize>Date "Interpret 2-digit years as
between"

Maybe back to 2001 or similar. Windows default is 2029.

BTW..........how are you getting Excel to handle dates prior to 1900?

Are you using the 1904 Date System?


Gord Dibben MS Excel MVP

 

[Dwight]

Gord

I have been using Excel for about 2 months. I haven't set anything special,
just using the default settings. All dates before 1900 are saved as a four
digit year and everything else as a two digit year. I haven't changed any
settings on my OS(XP Pro). This problem just started today. I don't know
much about Excel and don't know anything about the 1904 Date System. That's
a new one on me.

Dwight

 

[Gord Dibben]

Dwight

The "century" settings I referred is found in your Windows Regional Settings,
not in Excel.

If you were entering dates prior to 1900 you must have been entering as text,
not "real" dates.

For more on how Excel handles dates see Chip Pearson's site.

http://www.cpearson.com/excel/datetime.htm#SerialDates

To handle dates prior to 1900 see John Walkenbach's site for his Extended Dates
add-in

http://www.j-walk.com/ss/excel/files/xdate.htm


Gord

 

[Dwight]

Gord

I found the "Century" settings and changed them to 1900-1999. Still didn't
work. As I work with genealogy records, I entered all dates as text. But,
sometimes I have to sort on the date column and they don't sort right as
text, so I have to convert them to a real date. I think this is where the
problem started. When I exported the files as text files, the dates after
1900 were saved as a two digit year. I haven't had any problems before, but
today things finally caught up with me. Now I have to go back and get my
backup files. I won't make that mistake again. Thank for your help.

Dwight

 

[Gord Dibben]

Thanks for clearing that up Dwight.

Gord

Gord

I found the "Century" settings and changed them to 1900-1999. Still didn't
work. As I work with genealogy records, I entered all dates as text. But,
sometimes I have to sort on the date column and they don't sort right as
text, so I have to convert them to a real date. I think this is where the
problem started. When I exported the files as text files, the dates after
1900 were saved as a two digit year. I haven't had any problems before, but
today things finally caught up with me. Now I have to go back and get my
backup files. I won't make that mistake again. Thank for your help.

Dwight