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.