Fixing short date strings in Textworks

by Peter Tyrrell Thursday, June 07, 2007 2:58 PM

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. comic

blog comments powered by Disqus

Month List