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

C

Cousin_Geo

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

Forcing a date entry format of dd/mm/yyyy 1
reformat dates to yyyy/mm/dd 5
How to enter a date in DD-MM-YYYY format 2
yyyy-mm-dd date format? 3
How to convert MM/DD/YYYY to Month 3
Sumproduct and weekday from mm/dd/yyyy format? 3
Changing date to eliminate day in format mm/dd/yyyy 1
Formula bar shows mm/dd/yyyy. To show dd/mm/yyyy. How? 2

Top