Link to home
Start Free TrialLog in
Avatar of jumptohigh
jumptohigh

asked on

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
Avatar of nutsch
nutsch
Flag of United States of America image

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
Avatar of jumptohigh
jumptohigh

ASKER

Hi Thomas,

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

Regards,
Mike
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
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.
ASKER CERTIFIED SOLUTION
Avatar of nutsch
nutsch
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks again!
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
How about putting a filter on your column (data filter), selecting the cells that don't contain @ and address it like that?