Ask Your Question
0

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

Hi,

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 4.3.0.4 on Microsoft Windows 7).

Thanks,

Leon

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
0

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

Comments

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
0

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
0

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

ratrace gravatar image

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

Hello,

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

Comments

Hi,

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
0

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

karolus gravatar image

Hallo

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

=YEAR(A2)

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

edit flag offensive delete link more

Question Tools

1 follower

Stats

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

Seen: 400 times

Last updated: Oct 20 '14