Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

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
0
jumptohigh
Asked:
jumptohigh
  • 4
  • 4
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
nutschCommented:
Type this after the start of the macro

On error resume next

It will do away with the stops
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now