Reformatting incorrect dates
 

Hello
I have basic VBA knowledge and am trying to solve the following problem In
Excel 2003:

I receive a database with a date column containing data gathered from 2
databases. The date is formatted in two different ways:

'15/07/2005
15.07.2005

I would like to change all dates formatted as character to "real" dates
which is the second option above (European format with dots).

I have tried recording the following:

' In Date column, replace / with .
Columns("A:A").Select
Selection.Replace What:="/", Replacement:="."

' Change date text to number
Range("N1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

It works manually but not when replayed as a macro. The dates (even those
that were originally) correct turn into characters ... if I use Edit / Clear
/ Formats none of the dates revert to numbers.

I really appreciate your help!

Thanks very much. KDJ

Reformatting incorrect dates
 

Hi KDJ,

With the dates selected,

Data | Text to Columns | Delimited | Next | Next | Check the Date option
button | Select a date sequence from the dropdown | Finish

This should convert the text values to date in your local settings format.

---
Regards,
Norman



"KDJ" wrote in message
...
Hello
I have basic VBA knowledge and am trying to solve the following problem In
Excel 2003:

I receive a database with a date column containing data gathered from 2
databases. The date is formatted in two different ways:

'15/07/2005
15.07.2005

I would like to change all dates formatted as character to "real" dates
which is the second option above (European format with dots).

I have tried recording the following:

' In Date column, replace / with .
Columns("A:A").Select
Selection.Replace What:="/", Replacement:="."

' Change date text to number
Range("N1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

It works manually but not when replayed as a macro. The dates (even those
that were originally) correct turn into characters ... if I use Edit /
Clear
/ Formats none of the dates revert to numbers.

I really appreciate your help!

Thanks very much. KDJ

Reformatting incorrect dates
 

Thanks!! Much easier solution. Just tested it and it works beautifully.
Thanks
KDJ

"Norman Jones" schrieb:

Hi KDJ,

With the dates selected,

Data | Text to Columns | Delimited | Next | Next | Check the Date option
button | Select a date sequence from the dropdown | Finish

This should convert the text values to date in your local settings format.

---
Regards,
Norman



"KDJ" wrote in message
...
Hello
I have basic VBA knowledge and am trying to solve the following problem In
Excel 2003:

I receive a database with a date column containing data gathered from 2
databases. The date is formatted in two different ways:

'15/07/2005
15.07.2005

I would like to change all dates formatted as character to "real" dates
which is the second option above (European format with dots).

I have tried recording the following:

' In Date column, replace / with .
Columns("A:A").Select
Selection.Replace What:="/", Replacement:="."

' Change date text to number
Range("N1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

It works manually but not when replayed as a macro. The dates (even those
that were originally) correct turn into characters ... if I use Edit /
Clear
/ Formats none of the dates revert to numbers.

I really appreciate your help!

Thanks very much. KDJ