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