Excel date format mismatch

Question

I have a sizable excel file with the date listed in one column. The issue is that the date in this column is shown in various ways, as follows:

12/5/17 
12/5/17 
15-05-17 0:00
19-05-17 0:00
19-05-17 0:00
22-05-17 0:00

Some of them include a time stamp, while others use the format dd/mm/yy or mm/dd/yy. I require a single mm/dd/yy form (no time stamp). I have tried the fundamental techniques, such as formatting the cell by selecting the category and selecting a single date format, but the values of m/d/yy h:mm;@ remain unchanged. I also tried using datevalue with the following formula to retrieve the value in a new column: DAY(D2) & "/" & YEAR(D2) & "/" & MONTH(D2) & "/" Once more, it is ineffective. Any solutions to resolve this would be greatly appreciated. 

Answer 1

Due to the fact that the "dates" appear in so many distinct formats, it appears as though they are text in most cases.

Click Data > Text to columns > Next > Next after selecting the dates. Mark the column as a date column in Step 3 of the process and choose DMY from the option to reflect the order of the source data. Once you click Finish, the dates will appear using the format that is set for your region.

Now you can strip the time by using a helper column with the INT() function or just format the values to show without the time.