Excel date conversion

I recently had to work with an Excel spreadsheet that originated as a data export from another system.  The problem was that all dates had been imported as string values -- that is, with the leading single quote.  I needed to create PivotTables/PivotCharts from the data, but none of the date grouping would work.  So what to do?

The first thought was to just change the format of the field.  Logical, yes?  Successful?  No.  The format field (as makes sense) is for formatting.  It has nothing to do with the intrinsic value of a field.

I considered writing a macro for it, and that would have worked, but I wanted to avoid macros.

What I ended up with was a multi-step process that was somewhat ugly (well, really ugly), but functional.  My original format was:

Jan 13 2007 2:30PM

I realized that much of the problem was that the date wasn't in the format that Excel wanted.  My first step was to remedy that.  I did tedious search-and-replace operations to replace "Jan " with "1/" and " 200" with "/200" to fix the month and years into 1/13/2007.  Then I changed "PM" to " PM" (and for AM).  This gave me a date that matched my locale settings:

1/13/2007 2:30 PM

The final step was to use the Text-to-Columns feature.  This works very much like the file import wizard.  I selected the column with the date and copied to two new columns using space delimiting and identifying the fields as Date format.  The final step was to add the date to the time to get a unified field, then copy the Value of the merged field back to the original.  Yikes!  A macro would have made that much easier, though it was only a one-time deal.  I really wish Microsoft could have had a built-in feature for converting Text-to-Date, or even a generic "cast" operation as with programming languages.

I kept thinking that DateValue would work, but with the format ("Jan" instead of "1") it just wouldn't parse it.  Maybe if DateValue supported a format string (specifying the ordering of the date parts within the string) it could have worked.  Upon reflection though, the Text-to-Columns steps were unnecessary.  Once the search-and-replace was done, DateValue would have worked fine.  It just needs the format right, then it works fine.  I could have done that, copied the cells, then pasted them back in place with Paste Values.  It would have been nice if it was better supported easier in the first place though!