Fixing short date strings in Textworks

The Problem

1000s of date strings in short date format like m/d/yy. Fine as long as system date settings assume month/day/year. Then system date settings change to day/month/year to conform with international standards. 1000s of date strings are misinterpreted.

E.g. 06/01/2007 Before = June 1, 2007 After = January 6, 2007

The Fix

1. Export date field and unique ID field to delimited text file.
2. Use regular expression to switch day and month:
   • find expression: (\d+)/(\d+)/(\d{4})
   • replace expression: \2/\1/\3
3. Import modified file into Excel or Access, treating the date string field as DateTime so it's interpreted as a proper date, not a string.
4. Change the format of the date field to a Long Date
   • Access query expression: Format([MyDate], "Long Date")
5. Import the file with long date back into Textworks, matching on unique ID field; replace field values.
6. Date strings are now in unambiguous Long Date format, e.g. MMM dd, yyyy.

UPDATE: This comic at xkcd.com is totally awesome. And relevant and stuff.