Can I change the format a dates in a column using a macro

Posted by Geoff Johnston on December 07, 2000 3:09 PM

I am on a dairy research station. Each month we get sent a text file with herd recording results. For some reason the date column looks like 20000712 (yyyyddmm).
I have been trying to use a macro to reformat to look like 2000/07/12 by inserting slashes. The resulting macro just repeats the value 2000/07/12 in all cells in the column. Any help appreciated.

Posted by Ivan Moala on December 07, 2000 3:30 PM

Geoff
This may help you out.
Select the date range then run this quick macro.

Sub Macro1()
Dim rg As Range
Dim ocell As Range
Dim temp As String

Set rg = Selection
For Each ocell In rg
temp = Left(ocell, 4) & "/" & Mid(ocell, 5, 2) & "/" & Right(ocell, 2)
ocell = temp
Next
End Sub

Ivan