How to change date format in VBA
 

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

How to change date format in VBA
 

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

How to change date format in VBA
 

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

How to change date format in VBA
 

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

How to change date format in VBA
 

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

How to change date format in VBA
 

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

How to change date format in VBA
 

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

How to change date format in VBA
 

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

How to change date format in VBA
 

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