We help IT Professionals succeed at work.

Changing Text into Date in VB

Medium Priority
302 Views
Last Modified: 2012-05-12
I have a series of column headings which are used to describe a date period, however they are stored as a number.


PERIOD1      PERIOD2      PERIOD3      PERIOD4      PERIOD5      PERIOD6      PERIOD7      PERIOD8
9042011      9302011      10072011      11142011      11212011      11282011      12052011      12122011

PERIOD1 is Cell "F1" and the date range is "F2:BE2"

I need to convert these into a MS Date format.  I am thinking that if I were to get the slash marks in the right spots (9/04/2011), the program will easily convert this into a date during a second import process. I thought that I might need to read the numbers and set them as temp variables tvDate = (4 characters from right), tvDay = (2 characters in mid) and tvMonth = (first 1 or 2 characters).

Then put all the temp values together again as a date formated "mm/dd/yyyy" and replace the field.

Any thoughts on this?

Comment
Watch Question

CERTIFIED EXPERT

Commented:
You can try a formula like this one

=VALUE(SUBSTITUTE(F2,RIGHT(F2,6),"")&"-"&REPLACE(RIGHT(F2,6),3,0,"-"))

and format the cell as a date
Most Valuable Expert 2012
Top Expert 2012

Commented:
That will work.  But if you're looking for a macro, you could use this:

 
Sub textToDate()

    Set Rng = Sheet1.Range("A2:H2")
    For Each mycell In Rng
        yr = Right(mycell.Value, 4)
        mo = Mid(mycell.Value, Len(mycell.Value) - 6 + 1, 2)
        da = Left(mycell.Value, Len(mycell.Value) - 6)

        mycell.Offset(1, 0).Value = Format(da & "/" & mo & "/" & yr, "MM/DD/YY")
    Next mycell
        
    
End Sub

Open in new window


See attached
convertToDate-r1.xls

Author

Commented:
Dlmile:

I used your code and it works fine on my test worksheet, however when I put it into my actual working Workbook, I got an error for mycell "Variable not defined".

It's strange as it worked once in my actual workbook...however Excel converted the date into a julian date.  I started looking at this and tested it again, and the code now asks for a variable.

Thoughts on this?  Thoughts on keeping the cell formated to mm/dd/yyyy?
Most Valuable Expert 2012
Top Expert 2012
Commented:
Try this - I didn't initially define any variables:

Once you give me feedback on how this is acting in your actual workbook, I can tweak accordingly.  Note the embedded code, you'll need to change the worksheet name and range of interest.

Here's the code:

     
Option Explicit
Sub textToDate()
Dim rng As Range
Dim myCell As Range
Dim wkb As Workbook
Dim wks As Worksheet
Dim yr As String, mo As String, da As String

    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Sheet1") '<- change this to your sheet
    Set rng = wks.Range("A2:H2") '<- change this to your range
    
    For Each myCell In rng
        yr = Right(myCell.Value, 4)
        mo = Mid(myCell.Value, Len(myCell.Value) - 6 + 1, 2)
        da = Left(myCell.Value, Len(myCell.Value) - 6)

        myCell.Offset(1, 0).Value = Format(da & "/" & mo & "/" & yr, "MM/DD/YY")
    Next myCell
        
    
End Sub

Open in new window


See attached,

Dave
convertToDate-r2.xls

Author

Commented:
That got me back on track.  I didn't realize that mycell should be defined as a Range.

Now, any thoughts on clearing the original format in the cell so it remains a date format?

Author

Commented:
I figured out the format issue.  I format the range to general before it was converted.

Perfect.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.