Excel (or other) macro for cleaning date data
 

Does anyone have a good macro for taking date data that are in a
variety of formats and making them consistent? By format, I don't mean
that the cells are formatted differently, but that the data are all
recorded as, say, a string, but they all look different, e.g.

"01/05/92"
"01-07-84"
"Jan 12, 1991"
"January 1, 2000"
"10 January '05"

Is there any macro that is written to recognize that, say, the first
two are in mm/dd/yy format and to put them into a consistent style,
that the second, third, and fourth are in different formats, and to
put them into mm/dd/yy, etc.?

Excel (or other) macro for cleaning date data
 

Record a macro and you will have one. It should include...
Replacing the quote marks and apostrophe with nothing (Edit | Replace)
Multiplying all of the dates by 1 (Edit | Paste Special | Multiply)
Changing the number format to mm/dd/yy (Format | Cells | Number (tab) | Custom
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





wrote in message
Does anyone have a good macro for taking date data that are in a
variety of formats and making them consistent? By format, I don't mean
that the cells are formatted differently, but that the data are all
recorded as, say, a string, but they all look different, e.g.

"01/05/92"
"01-07-84"
"Jan 12, 1991"
"January 1, 2000"
"10 January '05"

Is there any macro that is written to recognize that, say, the first
two are in mm/dd/yy format and to put them into a consistent style,
that the second, third, and fourth are in different formats, and to
put them into mm/dd/yy, etc.?

Excel (or other) macro for cleaning date data
 

Bliss/Ryan,

You could also use worksheet formulas.

IF you actually have the double quote marks in each cell, then try:

=DATEVALUE(SUBSTITUTE(A1,CHAR(34),""))

with the cell formatted for date, then copy down to match.

If you don't actually have the double quotes and were just using them in
your post, you could use

=DATEVALUE(A1)

Then copy the cells with your formulas, and paste special values, then
delete your original column of strings.

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
Does anyone have a good macro for taking date data that are in a
variety of formats and making them consistent? By format, I don't mean
that the cells are formatted differently, but that the data are all
recorded as, say, a string, but they all look different, e.g.

"01/05/92"
"01-07-84"
"Jan 12, 1991"
"January 1, 2000"
"10 January '05"

Is there any macro that is written to recognize that, say, the first
two are in mm/dd/yy format and to put them into a consistent style,
that the second, third, and fourth are in different formats, and to
put them into mm/dd/yy, etc.?

Excel (or other) macro for cleaning date data
 

In .com,
spake thusly:

"01/05/92"
"01-07-84"
"Jan 12, 1991"
"January 1, 2000"
"10 January '05"

Is there any macro that is written to recognize that, say, the first
two are in mm/dd/yy format and to put them into a consistent style,
that the second, third, and fourth are in different formats, and to
put them into mm/dd/yy, etc.?

Not sure about a macro, but you can just select the one with the
format you like, click the Format Painter in the Formatting Toolbar,
and drag or click the format along all that you want to use that format.
If you click twice quickly on the Format Painter, it will toggle
"locked" and you can click subsequently on various other cells and
ranges; then, when you're done, click again on the Format Painter
to toggle off.

--
dman