Convert MMDDYYYY String to MM/DD/YYYY

Bill Cannon

New Member
Joined
Jun 12, 2003
Messages
8
I'm using Excel 2000 and need to convert a column of dates in an Excel Spreadsheet into a csv file and then import it into another program. When I attempt to format the cells to the needed format it changes the dates. For example, the date 00/00/1914 when formated to date format changes to 2/23/4647. Any suggestions on how to change to the needed format?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Some of the dates have only the year, some have only a month and a year. I had to convert them all to MMDDYYYY format by adding leading zeroes. 00/00/1914 means an unknown month and day in the year 1914
 
Upvote 0
Since your dates aren't complete, you can't format them as dates. I think your best bet may be two convert them to text, since you want to export them to a CSV file.

For example, if in cell A1 you have 1914, then in cell B1, you can use the formula:

="00/00/" & A1

You will need to write a similar formula for the ones that have months (you didn't say how those were laid out).
 
Upvote 0
I'll give this a try. Any idea why my dates changed when I tried to format cells to date format? Is there a way to know if the Excel "dates" are in text or serial dates?
 
Upvote 0
For me 1914 formatted as mm/dd/yyyy appeared as 03/28/1905, which is what I would expect. I don't know how you got 2/23/4647.
 
Upvote 0
You are correct. I was looking at the wrong spreadsheet. What does that tell me about my data? Is it text, serial dates or what?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top