Need a script to format strings into date format in an Excel Column

Hello Experts,

I have an excel spreadsheet with a column for date.  However, the date wasn't entered in a consistent manner and in some rows, not entered at all.   Here is a snippet of the data in the column:

12/9/2007
42907
32109
3032007
92708
empty
41707
31211
33011
22512
8/2/2003
7/30/2005
empty
empty
1/8/2003
92101
80908
12/3/2006
112005
empty
61809
90110
4/4/2003
91506
52109
80201
11132004
122107
62608

I tried formatting the cells with the "date" category but that messed up the dates in the fields.

Is there a script that I can run that would format all dates as mm/dd/yyyy?

Thank you for your help,
Mike
jumptohighAsked:
Who is Participating?
 
nutschCommented:
Type this after the start of the macro

On error resume next

It will do away with the stops
0
 
nutschCommented:
Hi,

select your dates and try the attached macro

Sub CleanUpDates()
Dim rg As Range

For Each rg In Selection
    If Not IsDate(rg) Then
        Select Case Len(rg)
        Case Is > 6
            rg = DateValue(Left(rg, Len(rg) - 6) & "/" & Mid(rg, Len(rg) - 5, 2) & "/" & Right(rg, 4))
        Case 0
            rg = ""
        Case Else
            rg = DateValue(Left(rg, Len(rg) - 4) & "/" & Mid(rg, Len(rg) - 3, 2) & "/" & Right(rg, 2))
        End Select
    End If
Next
End Sub

Open in new window


Thomas
0
 
jumptohighAuthor Commented:
Hi Thomas,

Pardon for my ignorance, but how do I create and run the macro.

Regards,
Mike
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
nutschCommented:
Will you be running it in just that file, or in multiple files. If in the former, open the visual basic editor (with alt+f11), Insert \ Module and copy the code in the new module. Once the code is copied, you can run it from your workbook with Alt+F8

Thomas
0
 
jumptohighAuthor Commented:
When I run the script on the entire column, it stops and gives an error, but it doesn't say where the error is... I soon realized that it is happening when it encounters an incorrectly entered date (e.g., 222811).   So I need to do it in small batches so I can locate the mis-formatted date and then restart the script.  Otherwise it works very well!  Thank you.
0
 
jumptohighAuthor Commented:
Thanks again!
0
 
jumptohighAuthor Commented:
Hi Thomas,

I have an e-mail column in excel.  When originally entering the data, some folks did not put an e-mail address in the column, but rather some other text or numbers.  Do you have a script that can search and stop at "non-email addresses" in a column where I can then decide/place where that info should go and then continue searching for non-email address strings?

Thanks,
Rich
0
 
nutschCommented:
How about putting a filter on your column (data filter), selecting the cells that don't contain @ and address it like that?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.