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: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted