I have some date data in txt but some of them can not be read as a date in excel, how can i fix it?

Question

i used cell format in excel but it still doesn't work, for example, if i change the order for 11/12/1997 and 5/06/2006 it gives me Dec-11-1997 but there is no change for the second date (5/06/2006) it seems it can be read as a date and they can't be deducted.

Answer 1

Use Format Painter on the Excel tool bar, the icon looks like a paint brush.

Click on one of the cells that shows the date formatted correctly.

Click on the Format Painter icon.

Click on the cell(s) with incorrect date format.

Good Luck

Answer 2

Say the date is in cell A1:

This function: DATE(year, month, day), combined with various text formulas will convert the text into a date format:

=DATE(RIGHT(A1,4), MID(A1,FIND("/",A1)+1,2), MID(A1,1,FIND("/",A1)-1))

Put this formula in a spare column somewhere, and change A1 to the cell with the first date in it.  Copy the format along the length of your date list.  Once you are happy with the results, you can replace the formulas with the date values by copying the new list, then right-click choose "paste special" and select "Values".