change the century

K

Keith

I have pulled into Excel a lot of information from a database, unfortunately
the database has only 2 characters for the year in a date field do people are
having their dates of birth set as 24th June 2047

How can I correct this in Excel?
 
D

Dave F

How is the cell formatted? When I enter 6/24/47, I get June 24, 1947 returned.

Dave
 
K

Keith

When I input a date manually is is correct but when it is pulled in from a
database using a Query it's wrong.
 
D

Dave F

Can you modify the query to spell out the full four digit year? What is the
SQL you're using?

Dave
 
P

Peo Sjoblom

It's probably because it is seen as text, what happens if you format a cell
with those dates as general?
If it is a number it should return 53867

Regards,

Peo Sjoblom
 
G

Gord Dibben

Keith

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 2030 or similar. Windows default is 2029.


Gord Dibben MS Excel MVP
 
Top