Date conversion

[SITCFanTN]

I have a report that I download into Excel. The report has dates for many
months. The current date format is ymmdd (ie 60530) and I'd like to convert
it to an easier to read format like 05/30/06. Is there code that I can use
in an existing page setup macro to do this for all rows in the speadsheet?
I tried using the Data\Text to Columns\and changed the text to a long date,
but it did not do it properly, it read the 60530 as 5/30/1960! Oh is there
code to automatically add 200 before the 60530. I added that and ran the Data
Text to Column and it converted properly.
Thanks

 

[fazstp]

This will format dates in column A from row 2 down.

Sub FormatDates()
Dim RowNum As Integer
Dim NextValue As String
Dim NextDate As Date

For RowNum = 2 To Range("A1").CurrentRegion.Rows.Count
NextValue = Range("A" & RowNum).Value
NextDate = DateSerial(2000 + Mid(NextValue, 1, 1),
Mid(NextValue, 2, 2), Mid(NextValue, 4, 2))
Range("A" & RowNum).NumberFormat = "dd/mm/yyyy"
Range("A" & RowNum).Value = NextDate
Next RowNum
End Sub

Personally I find excel's handling of dates to be eratic at best. Even
when I force the dates into a format I want, if I copy the sheet to
another workbook the formats are totally screwed up. Then if you try to
format the column post-copy it just doesn't work.

 

[Norman Jones]

Hi SITCFanTN,

Try:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
Dim iLastRow As Long
Const dateCol As String = "A" '<<=== CHANGE

Set WB = Workbooks("YourBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

iLastRow = SH.Cells(Rows.Count, dateCol).End(xlUp).Row

Set rng = SH.Range(dateCol & "2:" & dateCol & iLastRow)

On Error GoTo XIT
Application.ScreenUpdating = False

For Each rCell In rng.Cells
With rCell
If IsNumeric(.Value) And .Value <> "" Then
.Value = 20 & .Value
End If
End With
Next rCell

rng.TextToColumns Destination:=rng(1), _
DataType:=xlDelimited, _
FieldInfo:=Array(1, 5)
XIT:
Application.ScreenUpdating = False
End Sub
'<<=============

 

[Norman Jones]

Hi SITCFanTN,

.Value = 20 & .Value

Should read:

.Value = 200 & .Value

 

[ward376]

With the weird date in the first column -

You could use a text function without VBA -

=VALUE(CONCATENATE(MID(A1,2,2),"/",RIGHT(A1,2),"/",LEFT(A1,1)))

You'll get the value for the date you want. You can format before or
after.

Or you could enter the function and format with VBA -

Sub convertDate()
With ThisWorkbook.Sheets("Sheet1").Range("a1") _
..CurrentRegion.Offset(1, 0)
..Columns(2).FormulaR1C1 = _
"=VALUE(CONCATENATE(MID(RC[-1],2,2),""/"",RIGHT(RC[-1],2),""/"",LEFT(RC[-1],1)))"
..Columns(2).NumberFormat = "mm/dd/yy;@"
..Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

What kind of source do you have? A lot of times you can do some
manipulation (auto)before export that will clear this kind of pain up.

 

[ward376]

This will convert the functions to values -

Sub convertDate()
With ThisWorkbook.Sheets("Sheet1").Range("a1") _
..CurrentRegion.Offset(1, 0)
..Columns(2).FormulaR1C1 = _
"=VALUE(CONCATENATE(MID(RC[-1],2,2),""/"",RIGHT(RC[-1],2),""/"",LEFT(RC[-1],1)))"
..Columns(2).NumberFormat = "mm/dd/yy;@"
..Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
..CurrentRegion.Offset(1, 0).Columns(2) = .CurrentRegion.Offset(1,
0).Columns(2).Value
End With
End Sub

 

[JOUIOUI]

HI Norman, How would I edit this code if I was just using it in the active
sheet? Thanks a bunch

 

[SITCFanTN]

HI Fazstp,

Thanks so much for the code, but for some reason I'm getting a compile sytax
error on the 2 lines shown with the blank rows around it. Any other help you
can give me is appreciated. Thanks

Sub FormatDates()
Dim RowNum As Integer
Dim NextValue As String
Dim NextDate As Date
For RowNum = 2 To Range("A1").CurrentRegion.Rows.Count
NextValue = Range("A" & RowNum).Value


NextDate = DateSerial(2000 + Mid(NextValue, 1, 1),
Mid(NextValue, 2, 2), Mid(NextValue, 4, 2))


Range("A" & RowNum).NumberFormat = "dd/mm/yyyy"
Range("A" & RowNum).Value = NextDate
Next RowNum
End Sub

 

[SITCFanTN]

HI Ward,

I'm getting a Compile Syntax error on the first two lines of code. Any
assistnace you can give me to get it to run is appreciated. I'm putting this
code in an existing module for the active document. Thanks

 

[ward376]

It should be one line; either just put it on one line or use line
continuation, a space and an underscore.

 

[ward376]

Thats for zap's code -

 

[Norman Jones]

Hi JOUIOUI,

HI Norman, How would I edit this code if I was just using it in the active
sheet? Thanks a bunch

Try changing:

Set WB = Workbooks("YourBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

to

Set SH = ActiveSheet