Merging Tables with Different Date Formats in PowerPivot

So in my prior post I detailed what it took to merge different data sources with different layouts and columns into a single table within PowerPivot.  While I was doing this one of the problems I ran into was the fact that there was no consistency within my data sources in regards to the date format.  Some of the date formats I found were:

• MM/DD/YYYY
• DD/MM/YYYY
• MM/DD/YY
• DD/MM/YY

On top of that I also had:

• Empty Date Fields
• Date Fields with Invalid Dates (i.e., 00 for the Month or Day)
• Date Fields with Impossible Dates (i.e., 2601 for the Year)

Merging Tables with Different Columns and Layouts in PowerPivot

So I have not really worked in-depth with PowerPivot up to this point.  I have played around with it but never really done anything substantial.  However, I recently embarked on a pilot project for a client where PowerPivot was potentially the right solution for them.  This client’s data resides in multiple systems, which is fairly normal now-a-days.  The kicker for them was that most of their systems are hosted by outside providers and the options to get data out of those systems is limited.  Some of the limitations are forced by the systems themselves while some are financial as the provider wants extra money for every change in the export format or for each export.  So basically I am dealing with data that comes to the client in 8 or 9 spreadsheets, all with different columns and all with different column names.  To top it off, not all the columns are consistent across the spreadsheets.