Convert '20110606 to a datevalue

[LuigiWriter2]

I copied a html table with service dates and perscription data from a website and copied it into Calc
So far so good.
Except, all the dates came over '2010105 in format and the best I can get is 08/07/-8577.

Why I am submitting this as a new question.
Searched and read help and searched the entire OO site probably using the the wrong terms, being a tech writer and methods analyst, not a programmer. couldn't find a topic or a tutorial
I've read the other posts on importing csv.
even used the exact copy of the find and replace illustration in
[Solved] Convert imported text date to numerical value
at http://forum.openoffice.org/en/forum/vi ... al#p210973

both with all the variants I could find in all the Regular Expressions.

What else I did.
20110105 I got after the CLEAN function.
This still does not satisfy the VALUE function [err =511 or other]

The [it is now] Data>text to columns [instead of tools>] whether the number is
'20110105 or 20110105, gets me 08/07/-8577 with the calc options set 0=1900

Tried ???????? replace with "????????" and got could not find.

It looks like I have to move the year somehow, and insert quotes and dashes to get VALUE to work.

I worked with the original Calc back in the eighties and been involved in computers since the all the memory you needed was IBM's 16K. But I am stumped I need a step by step tutorial on this one, because of traumatic brain damage. Any one know of one or can at least give me the bits so I can write one myself, which may mean moving to a text document and back.

I'm also confused about the acceptable signature thing, It is very particular about how I worded things, so I just kept it to what It accepted first.
Thanks, Luigi

[FJCC]

I would use another column to enter a function like

Code: Select all

=DATE(LEFT(A1;4);MID(A1;5;2);RIGHT(A1;2))

I assumed that your dates strings are in the form YYYYMMDD.
I thought setting the format to YYYYMMDD would work but I get large negative years after forcing a reevaluation of the cell, as you did. That seems like a bug to me but maybe I'm overlooking something obvious.

[acknak]

Another alternative.

If your dates imported as text, such as '20110606, you can convert them to proper dates as follows:

Make sure the cells (or column ) have a format like Number/General that allows numeric values.

Select the column

Edit > Find & Replace
Search for: ^([0-9]{4})([0-9]{2})([0-9]{2})
Replace with: $1-$2-$3
Options/Current selection only: YES
Options/Regular expressions: YES
Click "Replace all"

The Find & Replace changes the text values into the standard ISO date format, which Calc will accept as a date for any locale.

[Alex1]

When you paste a table of more than one line into Calc, a window called Text Import will appear. Here you can select the column with the dates and then select Date (YMD) as its column type.

[bobinclg]

My dates imported as text, such as '20110606. In the Text Import window, Other Options area, I ticked the box labelled Detect Special Numbers. Now dates import as date values, not text.

[Villeroy]

Except, all the dates came over '2010105

This is no date. It is an integer number unless you make it a text (zip code, item number). Even if I wanted to interprete that sequence as a date, I fail to decide between 2010/1/05 and 2010/10/5. How could a computer do it?
Of course you get 2 million, 11 thousand and 606 when you convert the string 2010105 to a number. What else do you expect and why?

The "special number" in the screenshot a US date and it will only work in US context. US context interpretes 1/2/2012 as 2nd of January. Any other context would interprete the same special number as 1st of February. In any non-US context "11/26/2011" is not a date at all because there is no 26th month. First of all you should ask yourself how all the supposed dates get into the sheet as text. The answer depends on your input data (each single character counts) and the locale context.

My dates imported as text, such as '20110606. In the Text Import window, Other Options area, I ticked the box labelled Detect Special Numbers. Now dates import as date values, not text.

Stop telling us even more bullshit.
20110606 always imports as an integer number (20 million, 110 thousand and 606). Why should a spreadsheet import a sequence of digits as anything but integer number? Because the first 4 digits look like a year not too far away from now? The string "20110606" is a plain integer but not a special number. "2011/06/06" and "TRUE" and "6E09" and "€1,234.98" and "3/4" are special numbers.
There are several ways to import a sequence of pure digits as text if you want it to be text (zip code, item number).
A sequence of 8 digits is iterpreted as an ISO date (YYYYMMDD) if you select the column in the preview of the import dialog and mark that column as "Date(YMD)". Anything else leads to integer or text.

[kingfisher]

I frequently copy several rows of data from my bank account on line. I use the option to paste as unformatted text (in the list which displays when you click the arrow attached to the tool bar paste icon). In the dialogue which appears, I remove the selection "Quoted field as text," select the option "Detect special numbers," select the separator (in my case 'tab'), select the columns containing dates and select the date format D/M/Y.

For your locale / language the date formats may be different.

[Villeroy]

I frequently copy several rows of data from my bank account on line. I use the option to paste as unformatted text (in the list which displays when you click the arrow attached to the tool bar paste icon). In the dialogue which appears, I remove the selection "Quoted field as text," select the option "Detect special numbers," select the separator (in my case 'tab'), select the columns containing dates and select the date format D/M/Y.

For your locale / language the date formats may be different.

No need to specify the types of your date columns if your dates have separators between year, month and day. In most cases the locale alone can specify all the details for all numeric fields. The column settings can be used for the exceptions to the locale rules or when you need to declare a column of integers as a date field (20121106 --> 2012-11-06).