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
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
ASKER
Hi Thomas,
Pardon for my ignorance, but how do I create and run the macro.
Regards,
Mike
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
Thomas
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again!
ASKER
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
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?
select your dates and try the attached macro
Open in new window
Thomas