How do I convert the date from 1900's to 1800's?
 

I'm working on a spreadsheet in which I'm using dates from the 1800's. Is
there a way to convert the date from 11/29/65 to 29 November 1865? I've
tried to change it, but of course it goes to 1965 instead. In the meantime
I've been changing each date manually, but I wanted to see if there was an
easier and quicker way to convert the dates. Thanks!

Hi Diana

Try
=MID(A1,4,2)&"
"&CHOOSE(LEFT(A1,2),"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"
18"&RIGHT(A1,2)

--
Regards
Roger Govier
"Diana" wrote in message
...
I'm working on a spreadsheet in which I'm using dates from the 1800's. Is
there a way to convert the date from 11/29/65 to 29 November 1865? I've
tried to change it, but of course it goes to 1965 instead. In the
meantime
I've been changing each date manually, but I wanted to see if there was an
easier and quicker way to convert the dates. Thanks!

Hi, thanks for answering. Not to sound like a total dunce...but where would
I put this formula?

~Diana~

"Roger Govier" wrote:

Hi Diana

Try
=MID(A1,4,2)&"
"&CHOOSE(LEFT(A1,2),"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"
18"&RIGHT(A1,2)

--
Regards
Roger Govier
"Diana" wrote in message
...
I'm working on a spreadsheet in which I'm using dates from the 1800's. Is
there a way to convert the date from 11/29/65 to 29 November 1865? I've
tried to change it, but of course it goes to 1965 instead. In the
meantime
I've been changing each date manually, but I wanted to see if there was an
easier and quicker way to convert the dates. Thanks!

Hi Diana
Assuming your dates are all in column A, then put the formula in B1 and copy
down.
If your dates are in another column, change the reference form A1 to the
corresponding column letter.
If you do not have an empty column adjacent to the data either insert a
column, or use the first column available to the right of your data.
If having made the conversion, you want to just keep the "new" data, copy
the complete range of converted data, go to the first cell containg data in
the old format and choose Paste SpecialValues.

If you are having any further difficulties, don't hesitate to post back.

--
Regards
Roger Govier
"Diana" wrote in message
...
Hi, thanks for answering. Not to sound like a total dunce...but where
would
I put this formula?

~Diana~

"Roger Govier" wrote:

Hi Diana

Try
=MID(A1,4,2)&"
"&CHOOSE(LEFT(A1,2),"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"
18"&RIGHT(A1,2)

--
Regards
Roger Govier
"Diana" wrote in message
...
I'm working on a spreadsheet in which I'm using dates from the 1800's.
Is
there a way to convert the date from 11/29/65 to 29 November 1865?
I've
tried to change it, but of course it goes to 1965 instead. In the
meantime
I've been changing each date manually, but I wanted to see if there was
an
easier and quicker way to convert the dates. Thanks!