date format cell

hi i have an excel data that first column is date and secound and third column is coresponding time and height
how can i changed all the format of date in to one format dd/mm/yyyy
because some of them is in mm/dd/yyyy format. i attached image and excel file.
(dd/mm/yyyy time height)
thanks so much
regard

It seems to me that where the day in any particular month is 13th or higher, then the correct date is shown, but if the day is 12 or less then the month and day have been transposed. You can put this formula in D1:

=IF(DAY(A1)>12,A1,DATE(YEAR(A1),DAY(A1),MONTH(A1)))

Format that cell as a date dd/mm/yyyy, and copy down by double-clicking on the fill handle (the small black square in the bottom right corner of the cursor, with D1 selected).

Then you can fix the values in that column (Copy | Paste Special | Values | OK, then press the <Esc> key), and then you can copy column D to paste over the values in column A.

Hope this helps.

Pete

Thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete