Hello All,
I am using Windows XP/Office 2003
I have many workbooks stored in C:\Temp
All these work books have date stored in mixed format in two columns
viz Column A and Column D as follows:


12/31/06 ---- stored as text
13/1/06
15/1/06
1/16/06 ---- stored as text
17/1/06
20/1/06


As can be seen from above data sample the dates are stored in mixed
format.

I have run the following macro which works for Column A only (for
Column D I need to change the Range every time and there are many
workbooks so it is very time consuming process

Sub test()
With ActiveSheet.Range("A1:A100")
..NumberFormat = "dd/mm/yyyy"
..Value = .Value
End With
End Sub

My requirement is to have a macro to open all the workbooks one after
another in Folder C:\Temp and run the macro on Sheet1 in Columns A and
D and convert the dates stored in mixed format to proper date format

Can this be achieved?

Thanks in advance

Rashid Khan

Sub test()
Dim sName as String
Dim sPath as String
Dim bk as Workbook
Dim rngA as Range, rngB as Range
sPath = "C:\Temp\"
sname = Dir(sPath & "*.xls")
do while sName < ""
set bk = Workbooks.Open(sPath & sName)
With bk.worksheets(1)
set rngA = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
set rngD = .Range(.Cells(1,4),.Cells(rows.count,4).End(xlup))
End with
With rngA
.NumberFormat = "dd/mm/yyyy"
.Value = .Value
End With
With rngD
.NumberFormat = "dd/mm/yyyy"
.Value = .Value
End with
bk.Close SaveChanges:=True
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Hello All,
I am using Windows XP/Office 2003
I have many workbooks stored in C:\Temp
All these work books have date stored in mixed format in two columns
viz Column A and Column D as follows:


12/31/06 ---- stored as text
13/1/06
15/1/06
1/16/06 ---- stored as text
17/1/06
20/1/06


As can be seen from above data sample the dates are stored in mixed
format.

I have run the following macro which works for Column A only (for
Column D I need to change the Range every time and there are many
workbooks so it is very time consuming process

Sub test()
With ActiveSheet.Range("A1:A100")
.NumberFormat = "dd/mm/yyyy"
.Value = .Value
End With
End Sub

My requirement is to have a macro to open all the workbooks one after
another in Folder C:\Temp and run the macro on Sheet1 in Columns A and
D and convert the dates stored in mixed format to proper date format

Can this be achieved?

Thanks in advance

Rashid Khan

One small change to Tom's code, change this line:
Dim rngA as Range, rngB as Range
to
Dim rngA as Range, rngD as Range

Mike F
"Tom Ogilvy" wrote in message
...
Sub test()
Dim sName as String
Dim sPath as String
Dim bk as Workbook
Dim rngA as Range, rngB as Range
sPath = "C:\Temp\"
sname = Dir(sPath & "*.xls")
do while sName < ""
set bk = Workbooks.Open(sPath & sName)
With bk.worksheets(1)
set rngA = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
set rngD = .Range(.Cells(1,4),.Cells(rows.count,4).End(xlup))
End with
With rngA
.NumberFormat = "dd/mm/yyyy"
.Value = .Value
End With
With rngD
.NumberFormat = "dd/mm/yyyy"
.Value = .Value
End with
bk.Close SaveChanges:=True
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Hello All,
I am using Windows XP/Office 2003
I have many workbooks stored in C:\Temp
All these work books have date stored in mixed format in two columns
viz Column A and Column D as follows:


12/31/06 ---- stored as text
13/1/06
15/1/06
1/16/06 ---- stored as text
17/1/06
20/1/06


As can be seen from above data sample the dates are stored in mixed
format.

I have run the following macro which works for Column A only (for
Column D I need to change the Range every time and there are many
workbooks so it is very time consuming process

Sub test()
With ActiveSheet.Range("A1:A100")
.NumberFormat = "dd/mm/yyyy"
.Value = .Value
End With
End Sub

My requirement is to have a macro to open all the workbooks one after
another in Folder C:\Temp and run the macro on Sheet1 in Columns A and
D and convert the dates stored in mixed format to proper date format

Can this be achieved?

Thanks in advance

Rashid Khan

On Feb 11, 1:25 am, "Tom Ogilvy" wrote:
Sub test()
Dim sName as String
Dim sPath as String
Dim bk as Workbook
Dim rngA as Range, rngB as Range
sPath = "C:\Temp\"
sname = Dir(sPath & "*.xls")
do while sName < ""
set bk = Workbooks.Open(sPath & sName)
With bk.worksheets(1)
set rngA = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
set rngD = .Range(.Cells(1,4),.Cells(rows.count,4).End(xlup))
End with
With rngA
.NumberFormat = "dd/mm/yyyy"
.Value = .Value
End With
With rngD
.NumberFormat = "dd/mm/yyyy"
.Value = .Value
End with
bk.Close SaveChanges:=True
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy

wrote in message

oups.com...



Hello All,
I am using Windows XP/Office 2003
I have many workbooks stored in C:\Temp
All these work books have date stored in mixed format in two columns
viz Column A and Column D as follows:

12/31/06 ---- stored as text
13/1/06
15/1/06
1/16/06 ---- stored as text
17/1/06
20/1/06

As can be seen from above data sample the dates are stored in mixed
format.

I have run the following macro which works for Column A only (for
Column D I need to change the Range every time and there are many
workbooks so it is very time consuming process

Sub test()
With ActiveSheet.Range("A1:A100")
.NumberFormat = "dd/mm/yyyy"
.Value = .Value
End With
End Sub

My requirement is to have a macro to open all the workbooks one after
another in Folder C:\Temp and run the macro on Sheet1 in Columns A and
D and convert the dates stored in mixed format to proper date format

Can this be achieved?

Thanks in advance

RashidKhan- Hide quoted text -

- Show quoted text -

Thanks Tom
Works great.....you are a great help always.
Also thanks to Mike for pointing out the typo.