Convert MMDDYYYY String to MM/DD/YYYY

[Bill Cannon]

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?

 

[Andrew Poulsom]

The data must be numeric. Formatting a text entry as a date has no effect.

 

[Bill Cannon]

The problem I have is that the program I am transferring the data into will not recognize it as a date unless it is in date format (numerical and not text). I keep hitting a wall because formatting the data in Excel as dates creates these strange results such as ########## or some different date than the original entry.

 

[Joe4]

Since the data is is numeric format, you must re-build the date in date format. If the current format of your date won't allow you to use Text to Columns, then use the DATE function.

For example, if in cell A1, you have 1914, you can convert it to 1/1/1914 by using the formula:

=DATE(A1,1,1)

NOTE: The format of DATE is =DATE(year,month,day)

 

[Andrew Poulsom]

What data do you have exactly? Please post a representative sample.

 

[Bill Cannon]

How do I attach a spreadsheet to a post so that I can provide sample data?

 

[Andrew Poulsom]

Use Colo's utility below, or just post a list in the body of the message.

 

[Bill Cannon]

Thanks but I finally figured out how to used the forumulas. Used formula to separate portions of cells and insert slashes. For example, I used =LEFT(A2,1)&"\"&MID(A2,2,2)&"\"&RIGHT(A2,4) to change the number 1011952 located in cell A2 to 1\01\1952.

I appreciate all the help.