date formattin

D

Duane

I have a date imported from another program. It shows up as 22.08.2008 for
Aug 22, 2008. It is not recognised as a date in excel. Can I convert it? I
have used the "replace" function and it works well. But I would like it to
be more automatic as the months change depending on when the data is
imported. There are also several of them (1400+).

How can I change 22.08.2008 to 22/08/2008? My computer is set to
English(US). Not sure if that is it?
 
G

Gary''s Student

To update in place (without a helper column), select the cells you want to
convert and run this macro:

Sub formater()
For Each cell In Selection
s = Split(cell.Value, ".")
cell.Value = DateValue(s(1) & "/" & s(0) & "/" & s(2))
Next
End Sub
 
A

AnotherNewGuy

Assuming the imported date is always formated with two-digit month and day
and 4-digit year, the following would work:

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
 
B

Bob Phillips

Select the Colum, goto Data>Text To Columns, Next through the first two
screens, then pick Date from the Column data format and Finish
 
Top