Date changing

M

Maclaren

I have a lot of dates that need to be changed from 1991-04-07 to a format
recognised by Excel as a date format, does anyone know a quick way of doing
this other than rewriting them all?
 
D

Dave O

Assuming your data is in column A, try this formula:
=DATE(LEFT(A1,4),MID(A1,6,2),RIGHT(A1,2))

The formula parses the text string into its representative numbers, and
supplies them as arguments in the DATE formula, which is designed to do
just this type of task. You can copy that formula down for whatever
number of rows are appropriate.
 
C

CLR

When I copied and pasted your date from your post to my XL97 it automatically
converted it to a date of 4/7/1991. Apparently your date in your workbook is
formatted as TEXT. It can be busted any number of ways. You can put a 1 in
an unused cell and select it and do Copy > then select your date cell(s) >
and do PasteSpecial > Values......this will turn 1991-04-07 into 33335, the
excel equivelent of April 7, 1991. You can then just format it for DATE as
you wish. Or, you can select the date column and do Data > TextToColumns >
and format it as date......

hth
Vaya con Dios,
Chuck, CABGx3
 
Top