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
[libreoffice-users] Re: Split Move/Copy Sheet item in sheet tab menu
Ok, I am on. This confirmation is reasonable and desirable. But the split copy/move does not change this in itself. /Kaj On 2015-02-18 16:22, David Lynch wrote: On 18/02/2015 15:04, Kaj wrote: It is that the *default* behaviour is to overwrite the target (and no warning is given). David Lynch The change might be ok, but in which way does that solve your problem? The target is overwritten in any case. Well yes, if you think you still are keeping the source and overwrite the target once more, then there is a difference. I have not seen it being a problem, but am not against the change. /Kaj Den 2015-02-18 14:00, skrev David Lynch: Reasonably, I have been asked by the developers to gain support from a wider body of users for a change in the user interface to Calc. My request is *bug 68582 https://bugs.documentfoundation.org/show_bug.cgi?id=68582* * * When you right-click a sheet tab in Calc you get a menu: Insert Sheet... Rename Sheet... Move/Copy Sheet... Tab Colour... etc I would like this menu to be replaced by: Insert Sheet... Rename Sheet... Move Sheet... Copy Sheet... Tab Colour... etc Currently when copying a sheet, you must remember to click the copy button on the next menu to copy rather than move the sheet. I have just overwritten data I needed by forgetting to click this button. * *The most important reason is that the default behaviour of the command is to move the sheet, and this can destroy valuable data *without warning*. If, as I have done, you forget to click the copy button, you overwrite and destroy your existing sheet. * *A less important reason is that the interface is inconsistent: in other menus and toolbars, the choice of move or copy is at the top level, not hidden a level lower.**There are 12 commands in the menu: adding a thirteenth would not make it over long.* *Please may I have support for this request.* *David Lynch* * -- 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: Split Move/Copy Sheet item in sheet tab menu
The change might be ok, but in which way does that solve your problem? The target is overwritten in any case. Well yes, if you think you still are keeping the source and overwrite the target once more, then there is a difference. I have not seen it being a problem, but am not against the change. /Kaj Den 2015-02-18 14:00, skrev David Lynch: Reasonably, I have been asked by the developers to gain support from a wider body of users for a change in the user interface to Calc. My request is *bug 68582 https://bugs.documentfoundation.org/show_bug.cgi?id=68582* * * When you right-click a sheet tab in Calc you get a menu: Insert Sheet... Rename Sheet... Move/Copy Sheet... Tab Colour... etc I would like this menu to be replaced by: Insert Sheet... Rename Sheet... Move Sheet... Copy Sheet... Tab Colour... etc Currently when copying a sheet, you must remember to click the copy button on the next menu to copy rather than move the sheet. I have just overwritten data I needed by forgetting to click this button. * *The most important reason is that the default behaviour of the command is to move the sheet, and this can destroy valuable data *without warning*. If, as I have done, you forget to click the copy button, you overwrite and destroy your existing sheet. * *A less important reason is that the interface is inconsistent: in other menus and toolbars, the choice of move or copy is at the top level, not hidden a level lower.**There are 12 commands in the menu: adding a thirteenth would not make it over long.* *Please may I have support for this request.* *David Lynch* * -- 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] Calc fails to warn when insert cell breaks sum(A1:A2)
First: I missed sending to the list, so here again is my posting. Ok, I admit. I am the moron. Still I do not see the problem. Calc does not behave the way describe. If you insert a new cell, all its neighbours are influenced, and you yourself chose how, via the dialogue: Move down , Move right, New line or New column (ok I did not quote the headers correctly, but I am convinced you understand). No other option is given. So after a cell insertion with option Move right the neighbours really have new positions one step ahead of the original one. To me, what you describe, Brian, the situation is not inserting a new cell, but a new value, possibly clearing the old one, into cell A1, without changing the structure. Am I correct? If so, the solution is already given by Mark in this thread, namely cell protection. If this is done in an appropriate way, and the user changes the value an allowed cell, no spreadsheet program in this world can hinder that (or warn for it). I honestly try to understand the core of the original question, but I cannot, sorry. At 2015-02-13 04:27, Brian Barker wrote: At 01:14 13/02/2015 +0100, you wrote: I think have a wee difficult to understand what you are doing, as I do not see any error. You put constants 1 and 2 in the cells A1 and A2 and a sum formula in A3. Then you insert an empty cell in A1 while moving the existing content in the cells one step to the right. Hence after the insertion A2 contains the constant 1, A3 contains the constant 2 and A4 contains the formula. All references are relative, so cell A4 now is = sum(A2:A3) giving the result 3, just as before. That the cell A3 computes 2 is evident as it contains the constant you put in cell A2 before the move. You are right that there is no problem to be solved here, but you've misunderstood the detail of the problem. Although you talk of moving content one step to the right, your subsequent description is of the situation if the insertion into A1 moves the rest of column A *down*. Instead, the questioner means what he says: he inserts a new, empty cell into A1, moving the whole of row 1 one place to the right. A2 still has 2 and A3 still has =SUM(A1:A2), so the formula now adds the 2 in A2 to the value of the new empty cell A1 - interpreted as zero, of course. Brian Barker - privately -- 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] Calc fails to warn when insert cell breaks sum(A1:A2)
I think have a wee difficult to understand what you are doing, as I do not see any error. You put constants 1 and 2 in the cells A1 and A2 and a sum formula in A3. Then you insert an empty cell in A1 while moving the existing content in the cells one step to the right. Hence after the insertion A2 contains the constant 1, A3 contains the constant 2 and A4 contains the formula. All references are relative, so cell A4 now is = sum(A2:A3) giving the result 3, just as before. That the cell A3 computes 2 is evident as it contains the constant you put in cell A2 before the move. So sorry, I am not clever enough to realize your problem. Den 2015-02-12 21:14, skrev Spencer Graves: I recently noticed that a complicated spreadsheet that had previously functioned correctly was giving wrong answers without warning. After the usual wailing and gnashing of teeth, I traced the problem to a cell containing =C4-SUM(G11:G1016)”. Further experimentation produced the following simple version of the problem: (1) Let A1=1, A2=2, and A3=sum(A1:A2); A3 computes here as 3. (2) Insert cell A1 shift right. (3) Observe: A3 now computes as 2. This is obvious in this case but far from obvious in a complicated spreadsheet, where the connection between A1 and A3 is obscure. In such cases, For an insert that would cause an error in a reference like A1:A2, I believe that Calc should issue a warning something like, “WARNING: Insert may change the answer computed in A3. Do you want to proceed?” I further think there should be no default and the user should be forced to select either “Yes” or “No”. This was observed in LO 4.3.5.2, LO 4..5.0.0.alpha0 2015-02-05 00:36:56, and MS Excell 2003 sp3. Should this be filed as a bug report or a feature request? If yes, which, and what message should display? Wikipedia says, A software bug is an error, flaw, failure, or fault in a computer program or system that causes it to produce an incorrect or unexpected result, or to behave in unintended ways.” I think this fits that definition. However, it may qualify as a feature request, because the fix is less than obvious (and it has been around for so long). Enjoy, Spencer -- 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] How to do this in Calc
Which language are you using in Calc? The commands are language dependant. Those I wrote is for an English version. /Kaj Den 2015-02-08 02:31, skrev J. Van Brimmer: See below... On Sat, Feb 7, 2015 at 4:59 PM, Kaj 70147pers...@telia.com wrote: If this is a simple string, then the extract is quite simple. But if it contains date formulas in a way, it is a wee more complicated. But let us start with the simple assumption, that the string is exactly the one you give, including e.g. the lacking space before to: Assuming your string is in cell A1, cell B1 could contain: =CONCATENATE(MID(A1,12,5), - ,MID(A1,34,5)) When I tried that I get in column B: e lnf - A wee more safe could be: =CONCATENATE(MID(A1,12,5), - ,LEFT(RIGHT(A1,8),5)) When I tried that I got in column B: e lnf - ormat -- 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] How to do this in Calc
If this is a simple string, then the extract is quite simple. But if it contains date formulas in a way, it is a wee more complicated. But let us start with the simple assumption, that the string is exactly the one you give, including e.g. the lacking space before to: Assuming your string is in cell A1, cell B1 could contain: =CONCATENATE(MID(A1,12,5), - ,MID(A1,34,5)) A wee more safe could be: =CONCATENATE(MID(A1,12,5), - ,LEFT(RIGHT(A1,8),5)) Both these give the result you wanted. But what if the date is not the same, which result do you wish then? In that case I think you should do transforms into date format (DATEVALUE), but much depends on what you really want. Den 2015-02-08 00:23, skrev J. Van Brimmer: Thanks for reading, but I just manually formatted one page, and then I can copy/paste to other pages. Sorry for any inconvenience. I'd still like to know how to do it though. I'm thinking a macro is really what I need. On Sat, Feb 7, 2015 at 2:22 PM, jerryvb jerry...@gmail.com wrote: Column A has the following data: 2015-01-06 00:00:00to 2015-01-06 01:00:00 I want a formula in column B to convert it to this: 00:00 - 01:00 This looks easy, I just don't have the know how to do it. It's basically a copy, minus the -mm-dd, and replacing the to with a dash. You can just point me in the right direction if you want, any help appreciated. Thanks -- View this message in context: http://nabble.documentfoundation.org/How-to-do-this-in-Calc-tp4139304.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] How do I move a formula without changing it?
You might have the answer in your own question, even I am not quite ready in my thinking about this. If you move the formula (e.g. via the sequence ctrl+X and ctrl+V at the new position, well you can do it via the edit menu instead) the references do not change, the formula still points at the same cells as in the first position. It is when you copy the formula the references change, as they are relative. However to really get them absolute, you have to anchor them with the $ sign. Den 2015-01-04 16:39, skrev Robert Peirce: I have some rate-of-change triangles I need to change. These show the annual rate of change for different numbers of years and look like this: 2010XXXXX 2011XXXX 2012XXX 2013XX 2014X Basically, I want to reverse the lines, moving the top to bottom and vice versa. I want to do this so the multiple years go out from the ending date instead of the starting date. Like this: 2010X 2011XX 2012XXX 2013XXXX 2014XXXXX If I just copy and paste and the cells aren't protected by '$' the cells change accordingly. I don't want that to happen. I want the formulae to reference the same cells. I just want to move them to another location. Is there any way to do this? -- 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
