Changing Date Format Doesn't work?

[geoffr]

Trying to change the date formatting from 21/08/1961 to 1961-08-21 in order to import into our database. However, when reformatting the column accordingly the date format does not appear to change.

Is there a trick or is this a bug?

thanks,
Geoff

[Villeroy]

You are dealing with text data where "21/08/1961" is just a character sequence of 8 digits and 2 slashes.

[geoffr]

Mmm so how can we convert this to a date. It works perfectly in Excel.

Geoff

[Villeroy]

No, it does not work perfectly well in Excel but that's a different story. Converting wrong data is one thing. I would bother if there are wrong data types in your document and how they got into the sheet. Ctrl+F8 highlights all values.

http://www.yogeshguptaonline.com/2009/1 ... -date.html

Many of us get into a situation when the dates in our data are coded as text. Excel does not recognise such text entries as date and we can not use them for any calculation purpose. However you can easily covert them to date by the help of excel formula.

It is easier to do the converion in Calc.

[acknak]

Here's one thing you can try:

Select the column with your (text) dates.

Data > Text to Columns

Delimiter: Tab (or any character that does NOT occur in the column)

In the preview grid, select the column (click in the header, marked "Standard") change the type to "Date (DMY)"

OK

Your text dates should now be converted to numeric values and should respond to the cell/number formatting to display whatever format you want.

Alternatively, and this may be preferable to preserve the text data, you can insert a new column and convert the text dates by a formula, something like

=DATE(RIGHT(A1;4);MID(A1;4;2);LEFT(A1;2))

where A1 contains a date as text in the format you showed above.

Using the formula means that the original data are preserved intact.

[Villeroy]

Data > Text to Columns

... which fails if the cells are text formatted.

=DATE(RIGHT(A1;4);MID(A1;4;2);LEFT(A1;2))

... which is the same method as described for Excel in my link http://www.yogeshguptaonline.com/2009/1 ... -date.html