Dates in Text and Date format, need help converting all to date format...

Hi i have an excel file, some dates are in text format, and some are already in the date format. I need some help with the use of formula to translate all the dates into a date format. I've always had trouble with this and would appreciate some guidance.

Thanks!! (excel file attached)

What is your current default date format ?

If you type
4/7/2016 into a cell, and format it as mmm dd yyyy
Does it show as Apr 7th, or Jul 4th ?

I think this should work

=IF(ISNUMBER(A2),A2,DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)))

Thanks but the cells already in date format are positioned as month, day, year, so that formula you gave me above actually, position them as day, month, year.. how can i change that?

i.e. April 5th, 2016 shows as 05/04/2016 with the formula above.

Can you respond to my question in post #2 ?

Also, WHICH dates are actually correctly shown as real dates?
Verify with
=ISNUMBER(H2) and Filled down
Which ones are showing as TRUE, and Which are FALSE ?

Yes your post two formula works for the ones that are text, but incorrectly for the ones that are already formatted as dates... the ones that are "FALSE" from above format properly, whereas the ones as "TRUE" do not.

I didn't propose a solution in Post# 2, only a question. (referring to the Post # shown at the top right corner of each post)

If the isnumber is TRUE, the formula i posted in post #3 will not actually change the date in the cell.

ok thank u, then this works, i guess the source file has the incorrect date thank u again

I think you just need to format the cells with the formula as dd/mm/yyyy