subject:"\[libreoffice\-users\] Re\: converting txt to dates"

Am 13.04.2015 um 15:54 schrieb James Knott:
 On 04/13/2015 09:38 AM, Andreas Säger wrote:
 This __happens__ to work for you. What does 12-04-2015 actually mean?
 On my system this will be interpreted as 12th of April. People with US
 locale will get a date value referring to the 4th of December.
 
 That is why people should be using only the ISO format.
 
 https://en.wikipedia.org/wiki/ISO_8601
 

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.



-- 
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Re: converting txt to dates

Am 13.04.2015 um 08:58 schrieb bhaumikdave:
 
 Hi, I got a simple solution to this. My date format in excel sheet was like
 12-04-2015.
 
 Excel was not recognizing it as date or Formatting cell to Date was helping
 me.
 
 So I selected and Copied entire column of date to New Blank Word document.
 
   Then I Again copied the date column from word document and came back to my
 original excel sheet.
 
 Then right click and press Pest Special --Paste as TEXT.
 
 Done. Now Excel will recognize it as date and will allow you to format cells
 any date format as you wish. 
 
 
 

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).



-- 
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Re: converting txt to dates

Am 13.04.2015 um 08:58 schrieb bhaumikdave:
 
 Hi, I got a simple solution to this. My date format in excel sheet was like
 12-04-2015.
 
 Excel was not recognizing it as date or Formatting cell to Date was helping
 me.
 
 So I selected and Copied entire column of date to New Blank Word document.
 
   Then I Again copied the date column from word document and came back to my
 original excel sheet.
 
 Then right click and press Pest Special --Paste as TEXT.
 
 Done. Now Excel will recognize it as date and will allow you to format cells
 any date format as you wish. 
 
 
 


This __happens__ to work for you. What does 12-04-2015 actually mean?
On my system this will be interpreted as 12th of April. People with US
locale will get a date value referring to the 4th of December.

 Then right click and press Pest Special --Paste as TEXT.

When you paste-special unformatted text into LibreOffie Calc you will
get the text import dialog. There you need to choose the right locale
(any non-USA in this particular case) and you need to check the special
numbers option. Otherwise you will get text values again.

Your MS Office may be easier BUT if the origin of the text data
differs from the office locale it may import wrong data again and again.




-- 
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Re: converting txt to dates

Am 13.04.2015 um 15:54 schrieb James Knott:
 On 04/13/2015 09:38 AM, Andreas Säger wrote:
 This __happens__ to work for you. What does 12-04-2015 actually mean?
 On my system this will be interpreted as 12th of April. People with US
 locale will get a date value referring to the 4th of December.
 
 That is why people should be using only the ISO format.
 
 https://en.wikipedia.org/wiki/ISO_8601
 

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.


-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: converting txt to dates

On 04/13/2015 10:16 AM, Andreas Säger wrote:
 But nobody does. Not my bank from where I import account statements,
 yahoo finance, amazon, ebay, you name it.

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.  ;-)

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?


-- 
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Re: converting txt to dates

Am 13.04.2015 um 10:56 schrieb bhaumikdave:
 What ever the wordings are, conveying idea is important. I am not The
 Expert but just want to solve the issue using more simpler method rather
 than formulating some creepy scripts. 
 
 
 

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.




-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: converting txt to dates

On 04/13/2015 09:38 AM, Andreas Säger wrote:
 This __happens__ to work for you. What does 12-04-2015 actually mean?
 On my system this will be interpreted as 12th of April. People with US
 locale will get a date value referring to the 4th of December.

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

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

-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: converting txt to dates

On 04/13/2015 12:30 PM, Tom Davies wrote:
 They prefer to have everything
 from August waaay before anything from March.

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.

-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: converting txt to dates

2015-04-13 Thread Piet van Oostrum

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 :)
-- 
Piet van Oostrum p...@vanoostrum.org
WWW: http://pietvanoostrum.com/
PGP key: [8DAE142BE17999C4]

-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: converting txt to dates

On 04/13/2015 12:37 PM, 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.

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

-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: converting txt to dates

On 04/13/2015 12:46 PM, Piet van Oostrum 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 :)

If relevant, then specify.


-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: converting txt to dates

2015-04-13 Thread Tom Davies

Hi :)
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 :)




On 13 April 2015 at 15:26, James Knott james.kn...@rogers.com wrote:

 On 04/13/2015 10:16 AM, Andreas Säger wrote:
  But nobody does. Not my bank from where I import account statements,
  yahoo finance, amazon, ebay, you name it.

 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.  ;-)

 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?


 --
 To unsubscribe e-mail to: users+unsubscr...@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



-- 
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Re: converting txt to dates

Am 13.04.2015 um 16:22 schrieb Andreas Säger:
 Am 13.04.2015 um 08:58 schrieb bhaumikdave:

 Hi, I got a simple solution to this. My date format in excel sheet was like
 12-04-2015.

 Excel was not recognizing it as date or Formatting cell to Date was helping
 me.

 So I selected and Copied entire column of date to New Blank Word document.

   Then I Again copied the date column from word document and came back to my
 original excel sheet.

 Then right click and press Pest Special --Paste as TEXT.

 Done. Now Excel will recognize it as date and will allow you to format cells
 any date format as you wish. 



 
 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).
 
 
 

How does your Excel handle the following data set?

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



-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: converting txt to dates

2015-03-24 Thread Kaj


Hi !

Now you happened to use the wrong conversion function, VALUE instead of 
DATEVALUE, so of course it did not work. But even with the correct 
function there seems to be some issues. According to the help text for 
the date acceptance pattern: besides local ways to write date, also the 
ISO standard is supported. This standard says that dates are written, 
like all numbers in the decimal system, with most significant values to 
the left and least significant figures to the right. Hence dates are 
written -MM-DD. I tested this in my computer and it worked very 
well. Conversely I had no success with the format DD/MM/ despite I 
had introduced this as a pattern.


I also tested some string manipulation to convert the string like this
=DATEVALUE(CONCATENATE(RIGHT(A1,4),-,MID(A1,4,2),-,LEFT(A,2)))
and that worked fine too. However this is much the same as using the 
DATE function, proposed earlier by you Andreas:

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

BUT! wrong! The DATE function converts numbers into a date value, so you 
have to convert the strings into numbers. Thus:

=DATE(VALUE(RIGHT(A4,4)),VALUE(MID(A4,4,2)),VALUE(LEFT(A4,2)))

Regarding DATEVALUE, it converts a string into a date value, but you 
have to take care of the date format if you do not use ISO. So you have 
to define the date pattern,
(menu) - Tools - Language Settings - Languages at Date acceptance 
patterns.


Already Miguel Ángel pointed this out, that the fields in the date could 
be wrong, e.g. English (USA), and he was right.
I have not succeeded in modifying the pattern field directly, despite 
this should be possible according to help. But to change the Local 
setting three lines above worked fine. Now the default setting in this 
field is English (USA) with the attached pattern M/D/Y while we need 
D/M/Y. This is the standard for English (UK). With this done, all worked 
fine for me in my testing.


Regarding the setting (menu) - Format - Cells: This applies just for the 
converted cell, how the date shall be presented, and there you can 
chose anything you want, the default English (UK) (which you chose 
above), German (Germany) which gives the ISO standard, or anything you 
want. This formatting must not be applied to the cell where the original 
date is (e.g. cell A1). That cell should be formatted as text.


Kaj

Am 2015-03-24 02:16, Andreas Säger schrieb:

Am 20.03.2015 um 12:21 schrieb Kaj:

No, this won't work. You've got to change the global locale option in
order to change the evaluation context for existing data.

Other method: =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))




Do you have the used date pattern defined? Look at (menu) - Tools -
Language Settings - Languages at Date acceptance patterns.



OK, I installed the latest LibreOffice and tested
=VALUE(20-03-1999) = Err:502 (invalid argument)
Then I added date pattern D-M-Y which did not change anything.

The one and only relevant setting for the conversion of already existing
text is the global application locale above date acceptance patterns
in the language options.





--
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: converting txt to dates

2015-03-24 Thread Kaj


Am 2015-03-24 18:22, Andreas Säger schrieb:

Am 24.03.2015 um 14:22 schrieb Kaj:

Hi !

Now you happened to use the wrong conversion function, VALUE instead of
DATEVALUE, so of course it did not work. But even with the correct
function there seems to be some issues. According to the help text for
the date acceptance pattern: besides local ways to write date, also the
ISO standard is supported. This standard says that dates are written,
like all numbers in the decimal system, with most significant values to
the left and least significant figures to the right. Hence dates are
written -MM-DD. I tested this in my computer and it worked very
well. Conversely I had no success with the format DD/MM/ despite I
had introduced this as a pattern.

I also tested some string manipulation to convert the string like this
=DATEVALUE(CONCATENATE(RIGHT(A1,4),-,MID(A1,4,2),-,LEFT(A,2)))

and so does =VALUE(CONCATENATE(RIGHT(A1,4),-,MID(A1,4,2),-,LEFT(A,2)))


and that worked fine too. However this is much the same as using the
DATE function, proposed earlier by you Andreas:
 =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

BUT! wrong! The DATE function converts numbers into a date value, so you
have to convert the strings into numbers. Thus:
 =DATE(VALUE(RIGHT(A4,4)),VALUE(MID(A4,4,2)),VALUE(LEFT(A4,2)))


Wrong. The DATE function calculates one integer day number from 3
numbers year, month and day.
Without VALUE(), =DATE(RIGHT(A4,4),MID(A4,4,2),LEFT(A4,2)) works as well
because Calc implicitly converts integer numerals (strings consisting of
digits only).
Not wrong, but possibly not fully exhaustive. Did I mention how many 
numbers that were input for the conversion? If my eyes are still working 
as expected I read the word numbers (plural) when describing the main 
procedure. Well, in one aspect I have to admit I was not fully informed. 
I was not aware of the implicit conversion of strings containing numbers 
into numbers. Good to know. However, despite this, I prefer to define 
this conversion explicitly to have full control and not being surprised 
at some possible change in the future.


There are no date values in spreadsheets. 0 formatted as date gives
1899-12-30, 36526 formatted as date gives 2000-01-01. Both values, the
date value and the integer are the exact same value displayed in
different number formats like you can display them in different fonts,
colors or sizes.
Well, you call them date numbers, and that is ok for me. But as they are 
not just any, but defined as the number of days passed after 1899-12-30, 
I cannot see anything wrong in calling them date value. And moreover 
they are not simple integers, since the time of day is included as a 
decimal part, just the way you describe in the next paragraph. Did you 
think I do not know that?


If the value is an integer day number without time, VALUE and DATEVALUE
return the exact same day number.

In English notation with point as decimal separator and comma as list
separator:
=VALUE(2000-1-1 12:00) = 36526.5 (full day number with time)
=DATEVALUE(2000-1-1 12:00) = INT(VALUE(2000-1-1 12:00)) = 36526
(integer day number only cutting of the time fraction of the day)
TIMEVALUE(3.14159) = MOD(VALUE(3.14159),1) = 0.5 (the fraction of
the day cutting off the integer day number).


All this conversion from strings to numbers is unrelated to the date
acceptance patterns that determines how Calc turns your keyboard input
into day numbers.





--
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Re: converting txt to dates

Am 25.03.2015 um 01:28 schrieb Kaj:
 Am 2015-03-24 18:22, Andreas Säger schrieb:
 Am 24.03.2015 um 14:22 schrieb Kaj:
 Hi !

 Now you happened to use the wrong conversion function, VALUE instead of
 DATEVALUE, so of course it did not work. But even with the correct
 function there seems to be some issues. According to the help text for
 the date acceptance pattern: besides local ways to write date, also the
 ISO standard is supported. This standard says that dates are written,
 like all numbers in the decimal system, with most significant values to
 the left and least significant figures to the right. Hence dates are
 written -MM-DD. I tested this in my computer and it worked very
 well. Conversely I had no success with the format DD/MM/ despite I
 had introduced this as a pattern.

 I also tested some string manipulation to convert the string like this
 =DATEVALUE(CONCATENATE(RIGHT(A1,4),-,MID(A1,4,2),-,LEFT(A,2)))
 and so does
 =VALUE(CONCATENATE(RIGHT(A1,4),-,MID(A1,4,2),-,LEFT(A,2)))

 and that worked fine too. However this is much the same as using the
 DATE function, proposed earlier by you Andreas:
  =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

 BUT! wrong! The DATE function converts numbers into a date value, so you
 have to convert the strings into numbers. Thus:
  =DATE(VALUE(RIGHT(A4,4)),VALUE(MID(A4,4,2)),VALUE(LEFT(A4,2)))

 Wrong. The DATE function calculates one integer day number from 3
 numbers year, month and day.
 Without VALUE(), =DATE(RIGHT(A4,4),MID(A4,4,2),LEFT(A4,2)) works as well
 because Calc implicitly converts integer numerals (strings consisting of
 digits only).
 Not wrong, but possibly not fully exhaustive. Did I mention how many
 numbers that were input for the conversion? If my eyes are still working
 as expected I read the word numbers (plural) when describing the main
 procedure. Well, in one aspect I have to admit I was not fully informed.
 I was not aware of the implicit conversion of strings containing numbers
 into numbers. Good to know. However, despite this, I prefer to define
 this conversion explicitly to have full control and not being surprised
 at some possible change in the future.


The topic is about string to number conversion. After the conversion you
have different cell values. A conversion between number and date does
not take place. Formatting does not convert anything, The formatted
values remain the same and all calculations yield the same results.
Apart from errors, Calc has only 2 data types number and text. Excel has
booleans as a separate data type. In Excel =TRUE=1 and FALSE=0 both
return FALSE. In Calc the same comparison returns TRUE because 1 and
TRUE are the exact same values of the same type.

The original posting indicates that text has been imported due to a
wrong locale.
If the text values were the result of the missing special numbers
option or the result of quoting or if they were explicitly marked as
text, DATEVALUE or VALUE would do the conversion from text to number.

The only locale unable to convert 14/03/2015 is the US locale and some
Latin American locales with the same MDY date pattern.
Thus we know that James tries to convert a normal date under a global
US locale. He does not tell us how the bad dates got into his sheet but
if there are more of these dates with smaller day numbers (day numbers 1
to 12), then he certainly has wrong numbers among the text values, dates
such as 12/03/2015 referring to the 3rd of December instead 12 of March.
In this particular case, you should really try to import the same data
again using a UK locale and special numbers as import options. If the
original file or the clipboard content is no longer available, the only
way to convert such a bad date column is the formula I provided.



-- 
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Re: converting txt to dates

Am 24.03.2015 um 14:22 schrieb Kaj:
 Hi !
 
 Now you happened to use the wrong conversion function, VALUE instead of
 DATEVALUE, so of course it did not work. But even with the correct
 function there seems to be some issues. According to the help text for
 the date acceptance pattern: besides local ways to write date, also the
 ISO standard is supported. This standard says that dates are written,
 like all numbers in the decimal system, with most significant values to
 the left and least significant figures to the right. Hence dates are
 written -MM-DD. I tested this in my computer and it worked very
 well. Conversely I had no success with the format DD/MM/ despite I
 had introduced this as a pattern.
 
 I also tested some string manipulation to convert the string like this
 =DATEVALUE(CONCATENATE(RIGHT(A1,4),-,MID(A1,4,2),-,LEFT(A,2)))

and so does =VALUE(CONCATENATE(RIGHT(A1,4),-,MID(A1,4,2),-,LEFT(A,2)))

 and that worked fine too. However this is much the same as using the
 DATE function, proposed earlier by you Andreas:
 =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
 
 BUT! wrong! The DATE function converts numbers into a date value, so you
 have to convert the strings into numbers. Thus:
 =DATE(VALUE(RIGHT(A4,4)),VALUE(MID(A4,4,2)),VALUE(LEFT(A4,2)))
 

Wrong. The DATE function calculates one integer day number from 3
numbers year, month and day.
Without VALUE(), =DATE(RIGHT(A4,4),MID(A4,4,2),LEFT(A4,2)) works as well
because Calc implicitly converts integer numerals (strings consisting of
digits only).

There are no date values in spreadsheets. 0 formatted as date gives
1899-12-30, 36526 formatted as date gives 2000-01-01. Both values, the
date value and the integer are the exact same value displayed in
different number formats like you can display them in different fonts,
colors or sizes.

If the value is an integer day number without time, VALUE and DATEVALUE
return the exact same day number.

In English notation with point as decimal separator and comma as list
separator:
=VALUE(2000-1-1 12:00) = 36526.5 (full day number with time)
=DATEVALUE(2000-1-1 12:00) = INT(VALUE(2000-1-1 12:00)) = 36526
(integer day number only cutting of the time fraction of the day)
TIMEVALUE(3.14159) = MOD(VALUE(3.14159),1) = 0.5 (the fraction of
the day cutting off the integer day number).


All this conversion from strings to numbers is unrelated to the date
acceptance patterns that determines how Calc turns your keyboard input
into day numbers.


-- 
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Re: converting txt to dates

2015-03-24 Thread V Stuart Foote

Andreas Säger wrote
All this conversion from strings to numbers is unrelated to the date
acceptance patterns that determines how Calc turns your keyboard input
into day numbers.

No, it is not just for keyboard entry! And, it is certainly applicable for
format conversion of non-local date formats entered as text strings.

The OP says his data is already entered in the sheet as text strings:

Column A has *text* strings that are DD/MM/ format.
I want to make them real dates...

So, needs an efficient way for those text strings to be converted to dates
(in interger value).

When the column selection is cell formatted as Date, these existing text
strings will show with a single quote preceding on the formula bar.

The existing text stings are forcibly recast, easily done with a Selction,
then an Edit - Find-Replace using Regular expression of ^[0-9] and
replaced with for the Selection--which will cast text strings to
numbers-- which now within a date field, as controlled by date acceptance
pattern, become valid dates.

http://nabble.documentfoundation.org/file/n4144356/Find_Replace_RegEx_convertStringsDates.png

So, for the example of existing text strings, enter the matching Date
acceptance pattern of D/M/Y (and suppress any others if needed)--and then
recast the text strings 'DD/MM/YYY as dates.

--
View this message in context:
http://nabble.documentfoundation.org/converting-txt-to-dates-tp4143826p4144356.html
Sent from the Users mailing list archive at Nabble.com.

--
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Re: converting txt to dates

Am 20.03.2015 um 02:28 schrieb James:
 Column A has text strings that are DD/MM/ format.
 I want to make them real dates.
 I tried these 2 datevalue formulas but I can't make it work.
 
 14/03/2015Err:502
 14/03/2015Err:502
 
 
 =DATEVALUE(TEXT(A1,##/##/))
 =DATEVALUE(A2)
 

Short and correct answer again:

1) Avoid text strings alltogether. Enter valid numbers into numeric
cells (no text formatting) and import/paste-special with the right
locale and special numbers option set.
The right locale for your 14/03/2015 is anything English but not US
English. The right locale for 03/14/2015 would be English(USA).

2) If things went wrong and you can't redo the import and you have
imported text values _mixed_with_wrong_dates_ (inverted day and month),
then you should switch to the right locale (British for 14/03/2015) and
apply this formula:

=IF(ISNUMBER(A1);DATE(YEAR(A1);DAY(A1);MONTH(A1));VALUE(A1))

which either inverts back the month and day portion of any wrong date or
converts any text date to the correct numeric cell value.
Then turn the formula results into constant numbers (copy 
paste-special values) switch back to your preferred locale and format to
your liking. Neither the locale nor the formatting will change any
correct value but the correct locale is required for the correct British
context of the string-to-number conversion of 14/03/2015. German,
Russian, French do work as well with D/M/Y dates but not US Enghlish.




-- 
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Re: converting txt to dates