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
- Export date field and unique ID field to delimited text file.
- Use regular expression to switch day and month:
- find expression: (\d+)/(\d+)/(\d{4})
- replace expression: \2/\1/\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.
- Change the format of the date field to a Long Date
- Access query expression: Format([MyDate], "Long Date")
- Import the file with long date back into Textworks, matching on unique ID field; replace field values.
- 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.