Convert Multiple Date Formats Into Standard Format

Some of this may be able to be accomplished by pattern matching, but the issue is that some date formats are ambiguous, especially those that are either mm-dd or dd-mm or some other combination of month and day.

For example 4/5/2016 can be either April 5th, 2016 or May 4th, 2016. Obviously 13/5/2016 is May 13th as is 5/13/2016. But there are 144 ambiguous dates (the first 12 days of the 12 months).

Under these conditions, the best I can do is get the year for you. I included a few formats that you didn't include such as variations of mm/dd/yyyy and m/d/yyyy. Where the month or day can be either one or two digits. The function works on matching patterns using the VBA LIKE function.

One surprise I had is that the default condition, meaning do this if none of the other patterns match is Year(Date). The first surprise was that I am passing a string that looks like a number (to cover your General case) and it still interpreted it as a date. The other surprise is I passed it mm/dd/yy and it also interpreted it as a date. However, I was planning on programming some "windowing" to determine if the YY was in the 20th or 21st century. YEAR() figured it out for me. I think what it is doing is if the last two digits of the year are greater than the current year, it is the 1900's otherwise the 2000's.

Obviously dates in the 1400's are Hijri dates otherwise Common Era. I do not know how to convert between the two date systems.

I can't get the days and months since some formats are dd/mm and others are mm/dd and I can't tell from the information given which format is intended.

If one of the numbers is greater than 12, then I know that it's the day and the other one must be the month, but that still leaves 132 cases where I can get it wrong (The first 12 days of the 12 months = 144 minus where the day equals the month in which case it doesn't matter.)