i want to convert date like 04/04/1961 to 1961-04-04 but a resul not all correctly
04/04/1961 1961-04-04
08/07/1976 1976-08-07
01/01/1935 1935-01-01
12/03/1971 1971-12-03
20/09/1953 20/09/1953 *
04/04/1961 1961-04-04
08/07/1976 1976-08-07
01/01/1935 1935-01-01
12/03/1971 1971-12-03
20/09/1953 20/09/1953 *
01/07/1966 1966-01-07
25/12/1962 25/12/1962
12/07/1945 1945-12-07
10/09/1958 1958-10-09
(Edited slightly for better readability by @Lupp )
What do you mean by the term “convert”?
If your dates are numbers counting the days from 1899-12-30 being day zero, and are just formatted the way shown, you don’t need any conversion. You will simply change the ‘Numbers’ format assigned to the respective cells, or -better!- assign a named cell style which has the appropriate ‘Numbers’ format already defined.
If you dates are actually text because they were imported this way or were returned by the TEXT() function, e.g, changing the ‘Numbers’ format won’t have any effect: there is no number to get the new format applied.
Very likely most of your examples were numeric dates just wrongly formatted, but two of them (guess which ones) were text.
Dates given as texts actually need a conversion to get numeric standard dates which then can be formatted as wanted.
You can do this conversion for the whole column using the tool ‘Data’ > ‘Text to Columns…’
Select the column.
Call 'Data' > 'Text to Columns'
Select the column header in dialog that pops up.
'Standard' as column header is Ok, but:
In addition go to 'Column type' and select there the valid sequence of D M Y.
OK
Dates in your column that were already numeric are not afflicted by the procedure independent of the numeric format they are set to. The converted days will first get the default date format of yor locale automatically again (Yes, it’s a mess), but now you can change it as needed.
Leaving the original data where they are (say, starting with cell A2), and producing the results in a free column, say B starting with B2, you can use the Formula =IF(ISNUMBER(A2);A2;DATE(1900+VALUE(RIGHT(A2;2));VALUE(MID(A2;4;2));VALUE(LEFT(A2;2)))) and fill it down. Form,atting the second step again.