converting txt to dates

users
#21

That is why people should be using only the ISO format.

https://en.wikipedia.org/wiki/ISO_8601

#22

Which script? You mean the ordinary, simple spreadsheet formula?

Can you accept that a general solution to the problem of wrongly
imported text data may be slightly more difficult than you think?
The original poster did not provide any technical information to his
particular problem and he did not respond to anybody. Therefore I
suggested a one-method-fits-all solution which involves a maximum of 6
simple steps:

1. adjust the locale setting if necessary
2. paste and adjust my spreadsheet formula into a cell
3. copy the formula down the column (double-click the cell handle)
4. paste-special resulting numbers over the wrong data
5. If 1. then switch back to your preferred locale
6. format to your liking

This will convert text dates as well as wrong dates (switched month and
day) in one go and won't take more than 30 seconds.

#23

But nobody does. Not my bank from where I import account statements,
yahoo finance, amazon, ebay, you name it.
Calc can handle crazy date formats, even those with written month names
in dozends of languages. But you need to specify your special
requirements. Importing 13-04-2015 works out of the box once you have
checked the "special numbers" option. In case of a US locale you need to
specify that this is meant to be a non-US date, "English (UK)" will
handle this correctly.

#24

Oh, and 90% of related questions is not about how to correctly import
text data. People want to know how to fix wrongly imported data which is
slightly more difficult than doing it right in the first place.

#25

What is particularly irritating about your oversimplified approach with
WinWord and Excel is that it is not reproducible with LibreOffice.
It may work with Calc and a plain text editor (or not, depending on the
outlined details).

#26

Well, I often see it, particularly on government forms. On the other
hand, I have often seen dates and wondered what format it was, as there
is nothing to indicate it or the date it actually represents. Some
times I can guess, based on context or by looking for other dates to
determine which format makes sense. i.e. a number greater than 12 is
likely not a month. :wink:

Given the amount of international interchange, even ignoring the fact
that local custom is often not followed, the only way to be sure is to
use the ISO spec and nothing else. Isn't it also part of that ISO
certification that companies follow for business practices?

#27

How does your Excel handle the following data set?

04/12/2015
04/13/2015
04/14/2015
04/10/2015
04/14/2015

#28

Hi :slight_smile:
I use it quite a lot.

It makes sorting files a lot easier. If all files were created or modified
on the date that is most relevant to the contents of the file then it would
be quite simple but life is seldom that simple ime.

Of course no-one in my office understands. They prefer to have everything
from August waaay before anything from March.
Regards from
Tom :slight_smile:

#29

Another thing that adds to the "fun" is the 12 hour clock, so that 12:59
AM is before 1:00 AM. Of course, another bit of nonsense is 12 AM or
PM. The precise moment of 12:00 is neither AM nor PM. It is the
boundary between the two. So, the correct terms are 12 noon or 12
midnight, if you insist on sticking with the 12 hour clock. The better
method is to use the 24 hour clock. This also brings the benefit of,
when used with the ISO date format, the digits are listed in order of
descending value, going from left to right. This makes sorting on date
& time much easier and allows for no ambiguity.

For example, I am posting this at 2005 04 13 12:37. There is absolutely
no doubt as to when that is.

#30

James Knott wrote:

> For example, I am posting this at 2005 04 13 12:37. There is absolutely
> no doubt as to when that is.

Except for the time zone :slight_smile:

#31

Ooops!!! Typo. That should be 2015, not 2005.

#32

If relevant, then specify.

#33

I am using US Locale i.e. (English) US so it uses dd-mm-yy format.

But your dates are in " mm-dd-yy" format. Inorder to convert 04-14-2015 to
14th April, 2015 , I need to change Date and Time settings from Control
panel.

I kept my US locale as it is and changed date format to "mm-dd-yy" and
converted your dates as 13-April-2015, 14-April-2015, and so on.

Hope this is useful.

#34

Yes It is 12-April-2015.

I am using US Locale i.e. (English) US and it shows 12-04-2015 as
12-April-2015.

#35

No idea what Excel does or not or what proper date handling would be
most ideal.
You may try this one however :wink:

http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates

Cheers,
Cor

#36

I think we should make this an easy hack to get it into core. The question
comes up relatively frequently and many times people are just confused as
to why numbers/dates aren't showing up right.

Cor - your thoughts since you developed the extension?

Best,
Joel

#37

Hi Joel,

I think we should make this an easy hack to get it into core.

https://bugs.documentfoundation.org/show_bug.cgi?id=33962

The question comes up relatively frequently and many times people are just
confused as to why numbers/dates aren't showing up right.

Cor - your thoughts since you developed the extension?

From one I remember one of the core developers once commented that this
is an example of something that is not too easy to implement in the
regular code.

CT2N works in current LibreOffice versions.

Cheers,

#38

Ah, you prefer using the Windows system panel for a simple text import?
Would you find that setting on a Mac?

And did you try out what Excel does when you do not adjust your system
settings? No? It will import text and/or wrong dates with twisted months
and days. Even the holy Excel is able to import wrong data when you are
unaware of system settings.

What is wrong with the locale setting right in front of your nose on the
_text_import_dialog_ of LibreOffice where you can choose anything
adequate without navigating the system panel?

If you know, that my dates had been exported in German, you can easily
import
13. Mai 2015|10.3256,98
Check "special numbers", German(Germany) language and | as column separator.

I agree that the "special mumbers" option is misleading (if not
obsolete) and that it should be checked by default, however ignoring any
options will lead to errors sooner or later. In fact this option remains
checked by default once it has been used. In the rare cases where this
option does the wrong thing, you may turn it of or explicitly mark the
column as "Text".

#39

You can't push dirty Basic hacks into the core.

The extension may convert text into wrong values or nothing at all for
the same reasons I've outlined.

13/5/2015 <--> 5/13/2015 <--> 13. Mai 2015
1.234 <--> 1,234