Brutal Date Format

We recently updated our main property management system and are now going through the first month since. Over and over again now I'm running up against an issue in the way the vendor decided to start treating dates. In the past they just used "MMM DD', which was fine. These converted into valid dates in Excel that assumed they were in the current year. All good!

In the recent update, the vendor decided to add the year, but not in a good way. Now my data looks like this:

MAY9/09

MAY10/09

MAY11/09

 

Unfortunately this does NOT translate well into a date format automatically. You'd think that it would convert when you pulled it through the text import wizard, but it doesn't. Had the dates had 2 digit days consistently, or a delimiter of some kind between the month and day I believe it would have worked. As it is, the MDY format messes up the dates with 2 digit days and ignores the single digit days completely, giving me an even bigger mess. But pulling them in as text means that they can't be used to drive date dependant formulas, and sort like this:

MAY1/09

MAY10/09

MAY11/09

MAY2/09

MAY20/09

 

This is very irritating, and many users would tell you that this isn't trivial to fix. I worked up the following formula so that I could convert the dates into real dates:

=DATEVALUE(LEFT(A9,3)&" "&MID(A9,4,FIND("/",A9,1)-4)&", "&2000+RIGHT(A9,2))

(It assumes that the date is in A9)

This works by feeding the DateValue function the date in a "MMM DD, YYYY" format, which it can interpret. Here's the breakdown:

  • Month: LEFT(A9,3) &" " returns the left 3 characters of the text string followed by a single space
  • Day: MID(A9,4,FIND("/",A9,1)-4)&", " returns the string in the middle starting with the 4th character, returning the number of characters between the slash and the 3rd character, plus a comma and a space
  • Year: 2000+RIGHT(A9,2) returns 2000 plus the right two characters

Hopefully we're not using this spreadsheet in 2100... I can safely say that it won't be my issue if we are. J

We have lodged a complaint with the vendor as this is stupid. Any financial program that exports financial data should export dates in a format that is compliant with the biggest spreadsheet program out there. I should not have had to waste time writing a formula like that above.

2 thoughts on “Brutal Date Format

  1. Yes, dates are problematic. But do they need to be. Why people refuse to standardize on the format of something as basic as a date is completely beyond me. ISO-compliant date format makes a lot of sense!

    Concerning your complaint, I would suggest you lodge another one with Microsoft for Excel's failure to recognise properly the date strings you mentioned.

    Cheers.

    John
    2009-06-04

  2. I agree on the ISO dates. Personally I have begun converting all my stuff to the yyyy-mm-dd format. In addition to being a standard it also makes stuff sort correctly if you preceede your files with it in that way.

    I wonder if I'd get any traction on the date thing with MS... I may just have to try that.

    Thanks!

    Ken

Leave a Reply

Your email address will not be published. Required fields are marked *