# Why is the date type not changed when I change it? [closed]

Hello! Have no idea, what is wrong! I can change number formats, with no problem, can align to centre, left, right, etc, but simply can't change the date format! I trieed like this:

What is the mistake? Please, help!

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-02-26 09:48:02.660366

Sort by » oldest newest most voted

Select the column, choose Find and Replace. Search for ' replace with nothing. Replace all.

That should tell Calc not to consider these cells as plain text but normal data.

more

Hello! Sorry, I tried, but nothing happened! Btw, if I search for, lets say, 11. to be replaced with nov., how does it know it should be the one in the middle, not the one in the beginnign( f.e. for 11.02.1995)???

Many thanks!

more

Look at the value in the Formula Bar. Do you see an apostrophe before the date? This means that the value in the cell is a plain text, but not the date. Just choose Edit-Find&Replace, set "Search for" to .+ (dot-plus), "Replace with" to & (ampersand), "Regular Expresssions" to "On" and press "Replace all"

more

1

Find & Replace window | More Options in "Search in" drop-down list also it must be selected "Values".

( 2013-09-16 21:57:08 +0200 )edit

Perhaps you can apply a formula like this (on other cell):

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

more

Please, what is this formula doing? what is right(A1;4) meaning? I do not need just first 4 cells, so it is probaly not meaning a1 to a4? Many thanks!

( 2013-09-16 16:37:13 +0200 )edit

function right(), mid(), and left() are functions to extract some character from a cell. right(A1;4) means retrieve the rightmost 4 characters from cell A1. Those functions are exactly equal to Excel function.

( 2013-09-18 03:47:32 +0200 )edit
1. Select the column
2. Go to "Data" and choose "Text to Columns".

It will remove the apostrophe. After change the cell format with "Format cells..."

more

## Stats

Seen: 4,421 times

Last updated: Jun 25 '14