I want to change the date format in VBA from "13.07.2006" to "20060713".

If I try to record a macro applying the Format, Format cells... function,
nothing happends.

The excel workbook has date values in column A with the wrong format
"13.07.2006". The correct format is "20060713".

I try to open the workbook and create a new column B with correct date format.
Then I try to overwrite column A with a pasteSpecial command.

When I run the macro, I return runtime error 1004.

Any suggestions to make this work?

Regards

Frank Krogh


Sub ChangeDateFormat()
strFilename = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFilename < "False" Then
Workbooks.Open strFilename
Columns("B:B").Select

Application.Workbooks(Workbooks.Count).Activate
With Selection
.Formula = "=CONCATENATE(LEFT(A,7,4),(LEFT(A,1,2),(LEFT(A,4,2 ))"
End With

Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub

If column A contains real Excel dates formatted like "13.07.2006" then I
suppose that they have a "dd.mm.yyyy" custom format code. Try to change their
format to "yyyymmdd":

Columns("A:A").NumberFormat = "yyyymmdd"

Regards,
Stefi

€žFrank€� ezt Ã*rta:

I want to change the date format in VBA from "13.07.2006" to "20060713".

If I try to record a macro applying the Format, Format cells... function,
nothing happends.

The excel workbook has date values in column A with the wrong format
"13.07.2006". The correct format is "20060713".

I try to open the workbook and create a new column B with correct date format.
Then I try to overwrite column A with a pasteSpecial command.

When I run the macro, I return runtime error 1004.

Any suggestions to make this work?

Regards

Frank Krogh


Sub ChangeDateFormat()
strFilename = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFilename < "False" Then
Workbooks.Open strFilename
Columns("B:B").Select

Application.Workbooks(Workbooks.Count).Activate
With Selection
.Formula = "=CONCATENATE(LEFT(A,7,4),(LEFT(A,1,2),(LEFT(A,4,2 ))"
End With

Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub

Try this code assuming General format for Column A:

Sub ConvertDate()
Dim x As Variant, LRow As Long
Dim Drng As Range, c As Range
LRow = Cells(Rows.Count, "A").End(xlUp).Row
Set Drng = Range("A1:A" & LRow)
For Each c In Drng
x = c.Value
c.Value = Format(Replace(x, ".", "/"), "yyyymmdd")
Next
End Sub

Mike F
"Frank" wrote in message
...
I want to change the date format in VBA from "13.07.2006" to "20060713".

If I try to record a macro applying the Format, Format cells... function,
nothing happends.

The excel workbook has date values in column A with the wrong format
"13.07.2006". The correct format is "20060713".

I try to open the workbook and create a new column B with correct date
format.
Then I try to overwrite column A with a pasteSpecial command.

When I run the macro, I return runtime error 1004.

Any suggestions to make this work?

Regards

Frank Krogh


Sub ChangeDateFormat()
strFilename = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFilename < "False" Then
Workbooks.Open strFilename
Columns("B:B").Select

Application.Workbooks(Workbooks.Count).Activate
With Selection
.Formula = "=CONCATENATE(LEFT(A,7,4),(LEFT(A,1,2),(LEFT(A,4,2 ))"
End With

Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If
End Sub

Thanks for the suggestion, but the problem is that the A column still display
the "13.07.2006" format after the NumberFormat = "yyyymmdd" command is
executed.


"Stefi" wrote:

If column A contains real Excel dates formatted like "13.07.2006" then I
suppose that they have a "dd.mm.yyyy" custom format code. Try to change their
format to "yyyymmdd":

Columns("A:A").NumberFormat = "yyyymmdd"

Regards,
Stefi

€žFrank€� ezt Ã*rta:

I want to change the date format in VBA from "13.07.2006" to "20060713".

If I try to record a macro applying the Format, Format cells... function,
nothing happends.

The excel workbook has date values in column A with the wrong format
"13.07.2006". The correct format is "20060713".

I try to open the workbook and create a new column B with correct date format.
Then I try to overwrite column A with a pasteSpecial command.

When I run the macro, I return runtime error 1004.

Any suggestions to make this work?

Regards

Frank Krogh


Sub ChangeDateFormat()
strFilename = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFilename < "False" Then
Workbooks.Open strFilename
Columns("B:B").Select

Application.Workbooks(Workbooks.Count).Activate
With Selection
.Formula = "=CONCATENATE(LEFT(A,7,4),(LEFT(A,1,2),(LEFT(A,4,2 ))"
End With

Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub

Please check your Windows language setting and format and format code of
column A and post these data!
Stefi


€žFrank€� ezt Ã*rta:

Thanks for the suggestion, but the problem is that the A column still display
the "13.07.2006" format after the NumberFormat = "yyyymmdd" command is
executed.


"Stefi" wrote:

If column A contains real Excel dates formatted like "13.07.2006" then I
suppose that they have a "dd.mm.yyyy" custom format code. Try to change their
format to "yyyymmdd":

Columns("A:A").NumberFormat = "yyyymmdd"

Regards,
Stefi

€žFrank€� ezt Ã*rta:

I want to change the date format in VBA from "13.07.2006" to "20060713".

If I try to record a macro applying the Format, Format cells... function,
nothing happends.

The excel workbook has date values in column A with the wrong format
"13.07.2006". The correct format is "20060713".

I try to open the workbook and create a new column B with correct date format.
Then I try to overwrite column A with a pasteSpecial command.

When I run the macro, I return runtime error 1004.

Any suggestions to make this work?

Regards

Frank Krogh


Sub ChangeDateFormat()
strFilename = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFilename < "False" Then
Workbooks.Open strFilename
Columns("B:B").Select

Application.Workbooks(Workbooks.Count).Activate
With Selection
.Formula = "=CONCATENATE(LEFT(A,7,4),(LEFT(A,1,2),(LEFT(A,4,2 ))"
End With

Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub

This really convert the date values as intended :-)

However, in each cell in column A only displays #####. When I point the
mouse over on of the cells, it shows the comment "Negative dates or times are
showed as ####".

So if I then just format column A as format category "General", the ####
disappears.

Thank you for the solution!



"Mike Fogleman" wrote:

Try this code assuming General format for Column A:

Sub ConvertDate()
Dim x As Variant, LRow As Long
Dim Drng As Range, c As Range
LRow = Cells(Rows.Count, "A").End(xlUp).Row
Set Drng = Range("A1:A" & LRow)
For Each c In Drng
x = c.Value
c.Value = Format(Replace(x, ".", "/"), "yyyymmdd")
Next
End Sub

Mike F
"Frank" wrote in message
...
I want to change the date format in VBA from "13.07.2006" to "20060713".

If I try to record a macro applying the Format, Format cells... function,
nothing happends.

The excel workbook has date values in column A with the wrong format
"13.07.2006". The correct format is "20060713".

I try to open the workbook and create a new column B with correct date
format.
Then I try to overwrite column A with a pasteSpecial command.

When I run the macro, I return runtime error 1004.

Any suggestions to make this work?

Regards

Frank Krogh


Sub ChangeDateFormat()
strFilename = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFilename < "False" Then
Workbooks.Open strFilename
Columns("B:B").Select

Application.Workbooks(Workbooks.Count).Activate
With Selection
.Formula = "=CONCATENATE(LEFT(A,7,4),(LEFT(A,1,2),(LEFT(A,4,2 ))"
End With

Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If
End Sub

Record a macro when you select that column of cells that look like 13.07.2006.

Then use data|text to columns
fixed width
Date format (dmy)
and then change the format to yyyymmdd

And you'll have your code that looks kind of like:

Option Explicit
Sub ChangeDateFormat()

Dim strFileName As Variant
Dim wkbk As Workbook

strFileName = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFileName < False Then
Set wkbk = Workbooks.Open(Filename:=strFileName)
With wkbk.Worksheets(1) 'do you know the name of the worksheet?
With .Range("a:a")
.TextToColumns Destination:=.Cells(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 4)
.NumberFormat = "yyyymmdd"
End With
End With
End With
End Sub




Frank wrote:

I want to change the date format in VBA from "13.07.2006" to "20060713".

If I try to record a macro applying the Format, Format cells... function,
nothing happends.

The excel workbook has date values in column A with the wrong format
"13.07.2006". The correct format is "20060713".

I try to open the workbook and create a new column B with correct date format.
Then I try to overwrite column A with a pasteSpecial command.

When I run the macro, I return runtime error 1004.

Any suggestions to make this work?

Regards

Frank Krogh

Sub ChangeDateFormat()
strFilename = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFilename < "False" Then
Workbooks.Open strFilename
Columns("B:B").Select

Application.Workbooks(Workbooks.Count).Activate
With Selection
.Formula = "=CONCATENATE(LEFT(A,7,4),(LEFT(A,1,2),(LEFT(A,4,2 ))"
End With

Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub

--

Dave Peterson

Thank you for another solution that also worked just fine. (I only had to
change the last End With to End If)


"Dave Peterson" wrote:

Record a macro when you select that column of cells that look like 13.07.2006.

Then use data|text to columns
fixed width
Date format (dmy)
and then change the format to yyyymmdd

And you'll have your code that looks kind of like:

Option Explicit
Sub ChangeDateFormat()

Dim strFileName As Variant
Dim wkbk As Workbook

strFileName = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFileName < False Then
Set wkbk = Workbooks.Open(Filename:=strFileName)
With wkbk.Worksheets(1) 'do you know the name of the worksheet?
With .Range("a:a")
.TextToColumns Destination:=.Cells(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 4)
.NumberFormat = "yyyymmdd"
End With
End With
End With
End Sub




Frank wrote:

I want to change the date format in VBA from "13.07.2006" to "20060713".

If I try to record a macro applying the Format, Format cells... function,
nothing happends.

The excel workbook has date values in column A with the wrong format
"13.07.2006". The correct format is "20060713".

I try to open the workbook and create a new column B with correct date format.
Then I try to overwrite column A with a pasteSpecial command.

When I run the macro, I return runtime error 1004.

Any suggestions to make this work?

Regards

Frank Krogh

Sub ChangeDateFormat()
strFilename = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFilename < "False" Then
Workbooks.Open strFilename
Columns("B:B").Select

Application.Workbooks(Workbooks.Count).Activate
With Selection
.Formula = "=CONCATENATE(LEFT(A,7,4),(LEFT(A,1,2),(LEFT(A,4,2 ))"
End With

Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub

--

Dave Peterson

Glad you got it working--sorry about the typo.

Frank wrote:

Thank you for another solution that also worked just fine. (I only had to
change the last End With to End If)

"Dave Peterson" wrote:

Record a macro when you select that column of cells that look like 13.07.2006.

Then use data|text to columns
fixed width
Date format (dmy)
and then change the format to yyyymmdd

And you'll have your code that looks kind of like:

Option Explicit
Sub ChangeDateFormat()

Dim strFileName As Variant
Dim wkbk As Workbook

strFileName = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFileName < False Then
Set wkbk = Workbooks.Open(Filename:=strFileName)
With wkbk.Worksheets(1) 'do you know the name of the worksheet?
With .Range("a:a")
.TextToColumns Destination:=.Cells(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 4)
.NumberFormat = "yyyymmdd"
End With
End With
End With
End Sub




Frank wrote:

I want to change the date format in VBA from "13.07.2006" to "20060713".

If I try to record a macro applying the Format, Format cells... function,
nothing happends.

The excel workbook has date values in column A with the wrong format
"13.07.2006". The correct format is "20060713".

I try to open the workbook and create a new column B with correct date format.
Then I try to overwrite column A with a pasteSpecial command.

When I run the macro, I return runtime error 1004.

Any suggestions to make this work?

Regards

Frank Krogh

Sub ChangeDateFormat()
strFilename = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFilename < "False" Then
Workbooks.Open strFilename
Columns("B:B").Select

Application.Workbooks(Workbooks.Count).Activate
With Selection
.Formula = "=CONCATENATE(LEFT(A,7,4),(LEFT(A,1,2),(LEFT(A,4,2 ))"
End With

Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub

--

Dave Peterson


--

Dave Peterson