Excel Date formatting function not working

So here is my original data<br>

there are two formats, one is yyyy-mm-dd and another one is mm/dd/yyyy.

I tried to use "Text to Column" and Contr+1 to change the format to yyyy-mm-dd, none of these two method working.
And also, I find if I try to copy the data, paste as value, it will shows<br>

The desirable one yyyy-mm-dd will present as 5 digit numeric value but mm/dd/yyyy won't change.

Use the formatting number option that you want. Ways to get there:

1. It is under the "Home" > Number section choose the drop down > "Date" or "Custom"
2. right click on the cell and choose "format cell" > "Number" > "Date" or "Custom"
3. Find a cell that has the right format and click on "Home" > "Format Painter" then click on the cell that has wrong format.

The default format depends on your country when you setup the software. In the USA the "Date" is MM/DD/YYYY and "Custom" is the method you choose like YYYY-MM-DD

Edit - based on Williams comment

Check if the values have a proceeding or trailing space or other none printing character. If there is one that exist you will probably need to remove it. If you don't have the space character problem then the values are probably coming in as a text type. To change to a number do the following:

1. Type 1 in an empty cell then copy the cell.
2. highlight all the dates and numbers. Right click on the highlighted cells.
3. In the menu choose "Paste Special..." > "Paste Special..."
4. A new menu should appear that has "Operation" choose "Multiply". This will change text values to number values.

Your difficulty is based on how Excel figures what it is looking at.

When it finds data in a column that has only enough characters to be one of your system's date formats, and only appropriate characters to their outputs (so, probably not if a "}" character appears in it, that kind of thing), it will try to interpret all values in the column as dates. It only looks at the first eight values for a column in deciding to do this for the whole column. (That can be changed, but it's "hard.)

Anything that is not a date when interpreted against your computer's operating system's default formatting choices (NOT Excel's settings of any kind, but your OS settings), will be passed as in the source and treated as text in the opened file.

Consequently, anything that does not translate to a date will not change when changing number formats because... it is not a number to begin with, not to Excel.

Worse, by the way, some things will translate, but wrongly, and you don't even expect that. For instance, your system seems to have a setting these values are tried against of "DD/MM/YYYY" (and others, which may work nicely or have this exact problem themselves). So "3/25/1964" presents the month as being "25" and it fails, coming through as text. But January 8 1964, or 8/1/1964 in the DD/MM/yyyy format of the system has an evil twin in the MM/DD/yyyy format. So if your data has August 1 1964 entered in it as 8/1/1964, as I might enter it here in the US, your Excel will bring it in as January 8, not August 1. And since this is seldom the kind of error looked for... you never notice it, until someday it matters.

You cannot do a lot about the above, unless the data contains information that lets you know a mistaken import has occurred. But if you know the data will never have that kind of problem, you can ignore it.

In just the data you provide, and not adding to your troubles with things like the above, the one kind of date you do not seem to be able to import as a date rather than a string is the kind that is actually a "MM/DD/yyyy" formatted entry in the CSV file. Since this is the only kind, that you do not have to deal with several kinds, you do not have the possibly extreme problem of separating them into kinds. So the problem is pretty easy, really.

Use a formula in another place. Often called a "helper column" it need not even be on the sheet your data is on. You can just insert a sheet to work on, then transfer the results, and delete the helper sheet.

The formula to use will do two things: 1) Check to see if there is a problem at all, i.e., is the value imported as an actual date, or is it not, and 2) If it is not already a date, decompose it into parts and create a date out of the parts. It uses the newer TEXTBEFORE and TEXTAFTER functions but there are easy ways to use older functions for this if needed.

=IF(  ISNUMBER(  A1  ),  A1,  DATE(  TEXTAFTER(A1,"/",2),  TEXTBEFORE(TEXTAFTER(A1,"/"),"/"),  TEXTBEFORE(A1,"/")  ))

ISNUMBER checks to see if the value in the cell is a number. Anything Excel has imported as a date will actually be a serial number that counts up by 1 each further day. So this function can identify all nicely imported values and the IF will just give that as the result.

If the test fails, the DATE function takes the three pieces gotten from decomposing the string into parts and creates an Excel date value.

You now have a range with all the imported data output as real date values. Copy it all, and Paste|Special|Values it onto the original range. Delete the helper range/column/sheet/whatever if you do not wish it to continue to exist (often one doesn't).

But... look into the date creation that led to the CSV file. Find out if the variation in date entries could include people entering in MM/DD/yyyy and DD/MM/yyyy methods because not all entries that Excel and the OS can make work in MM/DD will turn out to import correctly.