Fields in 2 columns on spreadsheet contains text 2011/03/18 (imported from external source from a date range). I want to convert this into a proper date value. Instead of using the function =DATEVALUE I can build this in via VBA and create a button to map to my macro. First step is to convert the text fields to be in number format of "General". The code below will achieve this for 2 field ranges on 1 sheet.
Sub convertdate()
'
' Convert date format Macro
'
Sheets("NEW Report").Select
Range("T3:T5000").Select
Dim Rng As Range
For Each Rng In Selection
If IsNumeric(Rng) Then
Rng.NumberFormat = "General"
Else
On Error Resume Next
Rng = DateValue(Rng)
On Error GoTo 0
Rng.NumberFormat = "General"
End If
Next Rng
Range("P3:P5000").Select
For Each Rng In Selection
If IsNumeric(Rng) Then
Rng.NumberFormat = "General"
Else
On Error Resume Next
Rng = DateValue(Rng)
On Error GoTo 0
Rng.NumberFormat = "General"
End If
Next Rng
End Sub
Then from Excel menu go to Format, Cells (Ctrl+1) and choose "Date" as the format.
Useful link to show you how to do date arithmetic: http://www.cpearson.com/excel/datearith.htm
