date formating

**JLDSALLTHAT** · 02-28-2007, 03:15 PM

OK,

I have a large file, that I have converted into excel, from abby scan to office. the large amount of cells contain, what are suppose to be dates.

the dates show up like this:::::::::: JAN 03 2006
MAR 06 2006

I need to find a way to get excel to recognize these as dates and format them like 1/3/2006 . this file I copy and paste into another program that will not recognize the above as a date format.

any help or suggestions. I could re type all the dates, but would take me days, as this is an extremely large file.

**daddylonglegs** · 02-28-2007, 05:47 PM

You can convert a single column of dates in that format using Text to Columns Function.

Select your column of dates

Select "Text to Columns" from Data menu

Select "Next" twice and at step 3 from "Column data format" select "date" and "MDY"

Finish

**oldchippy** · 02-28-2007, 06:21 PM

If you want a formula ??? you could use this,

=DATE(RIGHT(A1,4),VLOOKUP(LEFT(A1,3),$G$1:$H$12,2,FALSE),MID(A1,5,2)) custom format cell to mm/dd/yyyy

For this to work you would need to set up a LOOKUP table in this case G1:H12, G1:G12 being JAN to DEC and H1:H12 numbers 1-12