Re: [libreoffice-users] Re: converting txt to dates
bhaumikdave wrote on 13-04-15 08:58: Done. Now Excel will recognize it as date and will allow you to format cells any date format as you wish. No idea what Excel does or not or what proper date handling would be most ideal. You may try this one however ;) http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates Cheers, Cor -- Cor Nouws GPD key ID: 0xB13480A6 - 591A 30A7 36A0 CE3C 3D28 A038 E49D 7365 B134 80A6 - vrijwilliger http://nl.libreoffice.org - volunteer http://www.libreoffice.org - The Document Foundation Membership Committee Member -- 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 Tue, Apr 21, 2015 at 12:19 PM, Cor Nouws oo...@nouenoff.nl wrote: bhaumikdave wrote on 13-04-15 08:58: Done. Now Excel will recognize it as date and will allow you to format cells any date format as you wish. No idea what Excel does or not or what proper date handling would be most ideal. You may try this one however ;) http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates 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 -- *Joel Madero* LibreOffice QA Volunteer jmadero@gmail.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
Re: [libreoffice-users] Re: converting txt to dates
Hi Joel, Joel Madero wrote on 21-04-15 22:44: On Tue, Apr 21, 2015 at 12:19 PM, Cor Nouws oo...@nouenoff.nl wrote: 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, -- Cor Nouws GPD key ID: 0xB13480A6 - 591A 30A7 36A0 CE3C 3D28 A038 E49D 7365 B134 80A6 - vrijwilliger http://nl.libreoffice.org - volunteer http://www.libreoffice.org - The Document Foundation Membership Committee Member -- 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
Hi :) I think you meant Writer rather than Word! Also Calc rather than Excel. However those sorts of tricks often work in all sorts of different Office suites. I would probably have used a text-editor rather than W* to avoid any weird formatting creeping in but you did the same by paste-special - as unformattted text Nicely done and a good answer imo :) Regards from Tom :) On 13 April 2015 at 07:58, bhaumikdave davebhaumi...@gmail.com wrote: 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. -- View this message in context: http://nabble.documentfoundation.org/converting-txt-to-dates-tp4143826p4146086.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 -- 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
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
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
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
Re: [libreoffice-users] Re: converting txt to dates
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
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
Re: [libreoffice-users] Re: converting txt to dates
DO 2015-03-20 12:00, Andreas Säger wrote: Am 20.03.2015 um 05:56 schrieb Emil Payne: On 19/03/15 08:28 PM, James wrote: 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) Highlight the cells Right click FORMAT CELLS NUMBERS tab In the FORMAT CODE block at the bottom, put DD/MM/ 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. -- 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 Fri, 20 Mar 2015 12:00:02 +0100 Andreas Säger ville...@t-online.de wrote: Am 20.03.2015 um 05:56 schrieb Emil Payne: On 19/03/15 08:28 PM, James wrote: 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) Highlight the cells Right click FORMAT CELLS NUMBERS tab In the FORMAT CODE block at the bottom, put DD/MM/ 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)) Please leave original context in replies. Stripping original content makes it more difficult to track conversations without going back through the entire message thread. Thanks, Tom :-) -- You don't get to choose how you're going to die, or when. You can decide how you're going to live now. -Joan Baez ^^ --... ...-- / -.- --. --... -.-. ..-. -.-. Tom Taylor KG7CFC openSUSE 13.1 (64-bit), Kernel 3.11.6-4-default, KDE 4.11.2, AMD Phenom X4 955, GeForce GTX 550 Ti (Nvidia 337.19) 16GB RAM -- 3x1.5TB sata2 -- 128GB-SSD FF 36.0, claws-mail 3.10.1 registered linux user 263467 -- 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
