Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert string to a date | Excel Worksheet Functions | |||
How do I convert a number formated as a date to text in Excel? | Excel Discussion (Misc queries) | |||
extract numbers, convert to date | Excel Discussion (Misc queries) | |||
convert julian date to gregorian date | Excel Discussion (Misc queries) | |||
How do I convert a Julian date into a regular date? | Excel Discussion (Misc queries) |