converting text to date

  • Thread starter Thread starter Jordan Shoderu
  • Start date Start date
J

Jordan Shoderu

can anyone help me i have a field in my spreadsheet with dates but they are
imported as text (20040129) as you can see i need the whole coloum to be
converted in the normal type date stuff DD-MMM-YYYY (ie 07-Aug-2004)

can anyone give me a piece of code in VBA that will run throught and convert
the text into date

thank you

Jordan
 
Jordan,

You can to this with a formula in a helper column:
=DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))
Copy it down. Now you can hide the original column. Or to convert the
original column permanently, copy the helper, and over the original do
Edit - Paste Special - Values. Now delete the helper column.
 
You don't need code. Select your column, choose Data/Text to Columns...
Click Next, Next, select "YMD" in the Date dropdown, then click Finish.

You can automate this by recording a macro.
 
can anyone help me i have a field in my spreadsheet with dates but they are
imported as text (20040129) as you can see i need the whole coloum to be
converted in the normal type date stuff DD-MMM-YYYY (ie 07-Aug-2004)

can anyone give me a piece of code in VBA that will run throught and convert
the text into date

thank you

Jordan
Click to expand...
You can do it by formula, or by using the Data/Text to Columns wizard, but
here is some VBA code:

===========================
Option Explicit
Sub convertdate()
Dim c As Range
Dim yr As Long, mnth As Long, dy As Long

For Each c In Selection
If IsNumeric(c.Text) Then
yr = Int(c.Text / 10 ^ 4)
mnth = Mid(c.Text, 5, 2)
dy = Right(c.Text, 2)
c.Value = DateSerial(yr, mnth, dy)
End If
Next c

End Sub
==========================
--ron
 
Back
Top