Date: prev next · Thread: first prev next last
2012 Archives by date, by thread · List index

At 05:04 01/09/2012 -0700, James Majeski wrote:
How do I get rid of the single quote format character so I can work with the dates from imported cvs or xls files?

What you have is not dates as such, but date values expressed as text. How to solve your problem is difficult to say exactly, since date formats and behaviour are locale-dependent. And a successful technique depends on exactly what format the text dates are in. But the first thing is not to try to remove the quote character - since it is not really there! It is simply an indicator that the value has been stored as text even though it looks like a date (and no doubt means a date to you).

Here are two ideas:

o When you open a .csv file in LibreOffice, you should see a Text Import dialogue box. If you click the column headers, you can then select a column type from the drop-down list. Selecting an appropriate date format at this stage may help. As has already been suggested, the "Detect special numbers" option should also solve your problem at this stage.

o If you have text values representing dates in your spreadsheet, try a formula such as =VALUE(Xn) or =DATEVALUE(Xn) in another column. Fill this formula down the column. Once you have a formula that works, you can copy and paste the results back over the original text values if you wish, and then even delete the new columns. But you will have to freeze the results of your formulae. To do this, use Edit | Paste Special... (or right-click | Paste Special... or Ctrl+Shift+V) instead of ordinary Paste; in the Paste Special dialogue, ensure "Paste all" is not ticked and then that Formulas is not ticked (but "Date & time" is) as well.

I trust this helps.

Brian Barker

For unsubscribe instructions e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.