Ask Your Question

how to change format date

asked 2017-12-14 12:14:22 +0200

fahruddn gravatar image

updated 2017-12-14 14:18:10 +0200

Lupp gravatar image

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 )

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-12-14 14:04:04 +0200

Lupp gravatar image

updated 2017-12-14 14:17:17 +0200

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.

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-12-14 12:14:22 +0200

Seen: 102 times

Last updated: Dec 14 '17