Is there a way to format yyyy/mm/dd before 1900

G

Guest

I have a database with dates ranging from 1839 to the present. I tried to
format date as mm/dd/yyyy and everything works fine. When I needed to change
the format to yyyy/mm/dd, only the dates after 1900 were formatted. Is there
a way to do this without retyping every cell in the correct format. I have
over 28,000 records
 
P

Paul Cavanagh

Hi Geo

Excel can't work natively with dates before 1900 but
there is an add-in written on the site www.j-walk.com
that allows you to go back a few hundred more years.



Alternatively you could break the date up by using left,
mid and right if they are all the same witdth



OR if there is a delimiter i.e. a / then you could use
the text to columns feature and then concatenate the
columns back in the order that you want them



Best Regards

Paul
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Format date dd.mm.yyyy to dd/mm/yyyy 6
Date format pre-1900. 1
Formatting dates prior to 1900 7
Reading Date Formats e.g 20050801 (yyyy/mm/dd) 4
REPOST: Date format pre-1900. 2
Sorting dates before 1900 3
Ensure dates imported into Excel 2003 are shown in DD/MM/YYY form. 3
Incorrect Date Format 2

Top