We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2013-09-16 10:41:44 +0200

altair gravatar image

updated 2014-06-25 17:09:15 +0200

bencomp gravatar image

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: image description image description

What is the mistake? Please, help!

edit retag flag offensive 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

5 Answers

Sort by » oldest newest most voted

answered 2013-09-17 15:49:57 +0200

Chris Vella gravatar image

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.

edit flag offensive delete link more

answered 2013-09-17 13:32:47 +0200

MichaelB12 gravatar image
  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..."

edit flag offensive delete link more

answered 2013-09-16 14:18:17 +0200

Paijo gravatar image

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


edit flag offensive delete link 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!

altair gravatar imagealtair ( 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.

Paijo gravatar imagePaijo ( 2013-09-18 03:47:32 +0200 )edit

answered 2013-09-16 13:49:36 +0200

altair gravatar image

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!

edit flag offensive delete link more

answered 2013-09-16 11:38:39 +0200

JohnSUN gravatar image

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"

edit flag offensive delete link more



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

L-user gravatar imageL-user ( 2013-09-16 21:57:08 +0200 )edit

Question Tools

1 follower


Asked: 2013-09-16 10:41:44 +0200

Seen: 5,707 times

Last updated: Jun 25 '14