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
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).
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…
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.
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
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.
Change the number format for the cell to ‘All’.
Apply a “fake” editing to the cell (appending and deleting again a space, e.g.) or apply ‘Data’ > ‘Text to Columns…’.
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.
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).
@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.