normalizing dates

Select the entire "Date" column only
Select from the menu Data\Text to Columns

• Step 1 of 3: Delimited
• Step 2 of 3: un-check all of the delimiters
• Step 3 of 3: Column data format; Date
• Finish

This will convert all the Text-Dates into serial dates. There may be some date formats that Excel can't convert with this method. You may have to manually change those or use a formula. If you need help on those, give specific details.

For many date formats, you can do Data > Text to Columns, Finish, and Excel will convert text that looks like dates to dates, and then you can format them as you wish.

Unfortunately, though, Excel's dates start at Jan 0, 1900, and so won't recognize anything prior as a date, even if you enter them manually, e.g., 4/4/1892.

In VBA, dates can go back to the year 100, but I don't see how that would help.

Hi there,

Thanks for the quick response. I followed your instructions and it appears that many of the cells were in fact converted to "Date" format.

However, I still have lots (and lots) of cells like this: "17-Apr-1891"; "07-Jul-1890"; "08-Dec-1877"; etc.

Regarding the comment about Excel not recognizing any dates before 1900, is there any work-around for that? In essence, after I've cleaned up my data, I'd like to run pivots showing, say, "how many building permits were granted in Washington, DC in the year 1888".

Thanks!


Kelly

One way would be to put the year in a separate column as an integer. You could extract it with one formula from the post-1900 dates (=year(a1)) and a different formula for the pre-1900 dates which will be, per force, text (e.g., =--right(A1, 4)). You could pivot on that.

Edit: Walkenback has an add-in that supports pre-1900 dates. See http://books.google.com/books?id=dt...&resnum=3&ved=0CBwQ6AEwAg#v=onepage&q&f=false

Not sure how that would work for a pivot table.

There is an add-in you can download that gives you additional functions to work with pre-1900 text-dates e.g.; the ability to extract the year. This may help you create a Years column that you could use in a pivot table.

XDate: Extended Date Functions Add-In