|I don't think you need VBA code, but there are some things that are unclear:|
First, you didn't specify Column letters, so I am going to assume your values are in A:C. (Please read the How To referenced in my signature line to see the proper way to post data in this forum.)
Second, you didn't say it, but I am going to assume that the values in Column C are the only ones you are concerned about. The dates in Column A seem fine.
Third, is 2/24/2261 an error? As Razor2.3 intimated, there would need to be Time Travel involved for that to be a valid "Date of Diagnosis".
Fourth, I am going to assuming that the dates with dots are in the "format" of day.month.year since there is no month 22, 25, etc.
Fifth, the dates with the slashes are hard to make an assumption about since 2/24/2261 has to be month/day/year (since there is no month 24) but 11/02/09 could be month/day/year or day/month/year. For the purpose of this exercise I am going to assume that the format is month/day/year since that is the format of Column A.
Sixth, I am going to assume that you want the final output to be month/day/year since that is the format of Column A.
OK, after all that, here we go...
Put this in D1 and drag it down:
Here's how it works:
NOT(ISERR(MONTH(C1))) checks to see if the value is a valid date, e.g. 11/02/09. If it is, it simply returns that date.
If MONTH returns an error (#VALUE), the formula checks to see if the value is 8 characters long, meaning it has a 2 digit "day". If it's not 8 characters, it must have single digit date.
In either case, the SUBSTITUTE function replaces the dots with slashes and the RIGHT, LEFT and MID functions rearrange the strings to get it in the format of mm/dd/yy.
The LEN check is there to decide what values to use as the start_num arguments.
is use the MONTH function to check if the value is a valid date. If it is, the final value_if_true
This is the output I got when I used that formula:
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.