Date from text to Date format

[Nise]

Hi all,
I have a date currently as text format "2000/Jan" and so forth. I wish
to convert this to a date format as "Jan-2000" is there a way to
accomplish this easily with a macro? I don't have much experience
programming so any help would be appreciated. I have a huge dataset
that this needs to be done for thus it would optimal to have a macro
solution.
Cheers,
N

 

[Anne Troy]

Well, it won't convert it to a date value, but it'll change it to read the
way you want:
=right(a1,3)&"-"&left(a1,4)
Copy down. A macro would likely take you a lot longer to get.
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com

 

[Nise]

Thanks for your quick response. That is how I've been going at it. Any
way to have the program copy all of the cells in that column transfer
them to another sheet, apply the formula and copy back to the original
with the updated format?

 

[Nise]

For example, I have this much written thus far:

Sub ChangeToDate()

Dim wks As Worksheet
Dim col As Long

Set wks = ActiveSheet
With wks
col = ActiveCell.Column

.UsedRange = .Range(.Cells(2, col), .Cells(2000, col))

For Each cell In wks.UsedRange
If cell.????? Then
//NEED code here
End If
Next

End With

End Sub

 

[Dave Peterson]

Maybe something like:

Option Explicit

Sub ChangeToDate()

Dim wks As Worksheet
Dim col As Long
Dim myRng As Range
Dim myCell As Range
Dim myDate As Date

Set wks = ActiveSheet

With wks
col = ActiveCell.Column
Set myRng = .Range(.Cells(2, col), .Cells(.Rows.Count, col).End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
'2005/Jan
If Len(.Value) = 8 Then
On Error Resume Next
myDate _
= CDate(Mid(myCell.Value, 6) & " 1, " & Left(myCell.Value, 4))
If Err.Number <> 0 Then
'not a date
MsgBox "Error with: " & myCell.Address(0, 0)
Err.Clear
Else
.NumberFormat = "mmm-yyyy"
.Value = myDate
End If
On Error GoTo 0
End If
End With
Next myCell

End Sub

 

[Nise]

Thank you so very much. You are a lifesaver/timesaver. I would give you
1 million points if I could.

 

[Gord Dibben]

Nise

Worked for me this way.......

Data>Text to Columns>Next>Next>Column Data Format>Date>YMD>Finish

Format to mmm-yyyy if necessary.


Gord Dibben Excel MVP