Ask Your Question

Functions ignored in Calc [closed]

asked 2014-10-20 12:01:29 +0200

leonstr gravatar image

updated 2016-03-05 09:14:40 +0200

Alex Kemp gravatar image


I've just saved a CSV as an ODS file and want to add some functions.

So let's say I've got an date in A2 "2014-10-20" and I want to get the year, in B2 I enter "=LEFT(A2,4)".

But in B2 I've now got "=LEFT(A2,4)", not "2014".

What have I done wrong?

(LO on Microsoft Windows 7).



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-03-05 09:14:53.047601

4 Answers

Sort by » oldest newest most voted

answered 2014-10-20 13:03:53 +0200

Lupp gravatar image

But in B2 I've now got "=LEFT(A2,4)" ...

The cell containing your formula is formatted by the code "@" under 'Format Cells' > 'Numbers'. In this case the formula will not be evaluated but be treated as text.

1) Change the number format for the cell to 'All'.

2) Apply a "fake" editing to the cell (appending and deleting again a space, e.g.) or apply 'Data' > 'Text to Columns...'.

3) Very likely the cells containing the dates imported from CSV are also formatted "@". To get them contain "true dates" you should apply the procedure of setting number format 'All' and applying 'Data' > 'Text to Columns...' to them, too. Dates are numeric values in spreadsheets which simply are displayed in a specific number format. A proper format will be applied by 'text to Columns...' in the second step automatically.

edit flag offensive delete link more


You're right on both counts. Surely some LO behaviour has changed here, I've done this a few times in the past and didn't have to do this. But many thanks to you (and everyone who responded).

leonstr gravatar imageleonstr ( 2014-10-20 13:31:47 +0200 )edit

answered 2015-09-28 11:09:21 +0200

CrlITA gravatar image

@Lupp is right - Calc is somehow reading your strings as "TEXT" and not as a formula to be calculated.

Not a real solution but a useful workaround in case you have too many formulas to change manually: copy and paste your sheet into a text program (kate, geany, gedit...) and then copy and paste it again into a new sheet. Now formulas should work; you can just get formatting from the old sheet and have a brand-new working sheet.

edit flag offensive delete link more

answered 2014-10-20 12:11:18 +0200

ratrace gravatar image

updated 2014-10-20 12:12:21 +0200


from the menu "Tools - Options - LibreOffice Calc - View" see if it is checked the first box which says formulas. If it is then uncheck it.

A shortcut you can use from your keyboard is ctrl + ~ (below the esc button).

edit flag offensive delete link more



The option says "Formulae" in my version/locale but it's unchecked. I've tried checking and unchecking it but still the same I'm afraid...

leonstr gravatar imageleonstr ( 2014-10-20 12:19:08 +0200 )edit

I'm copying another advice that i found in a similar question.

Hi, see your cells format. Maybe they are formated as Text. In this case, formulas doesn't work. So, change it and try again.

See if your cell's format is text and change it to number or date.

ratrace gravatar imageratrace ( 2014-10-20 12:23:34 +0200 )edit

answered 2014-10-20 12:40:34 +0200

karolus gravatar image


If your Date in A2 is real Date-Value and not Text, you should simply use


This Formula returns the Year also as Number not as Text as your Formula

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-10-20 12:01:29 +0200

Seen: 400 times

Last updated: Oct 20 '14