How to change two different date format into single date format in excel

Question

I have a different date format(YYYY.MM.DD and MM/DD/YYYY) in my date column in Excel. As a final result, I want one date format ie MM/DD/YYYY. What is the easiest way to achieve it?

Here is an image of my data.on Right-click and Format cell it looks like the below.

The final output I want to like is this. I have tried the Text to Columns option as well but it didn't work.

Answer 1

With data in A2, in B2 enter:

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

and apply your format to B2.

Then copy B2 downward:

The formula converts any text values in column A into true Excel dates in column B.