Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

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.

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

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.