Description: Im looking for Non-Volatile functions. Steps to Reproduce: 1.Open Calc and in cell B1 enter: =IF(A1="Blabla";NOW();3"") 2.In cell A1 enter "Blabla" 3.In an other cell enter something else. Actual Results: After entering something in an other cell, the time in cell B1 changed. Expected Results: The time in cell B1 should be a 'timestamp', not changing each time something changes in a cell. That is why I like to have Non-Volatile function(s): NOW.NV instead of NOW(). Reproducible: Always User Profile Reset: No Additional Info: Version: 7.5.1.2 (X86_64) / LibreOffice Community Build ID: fcbaee479e84c6cd81291587d2ee68cba099e129 CPU threads: 4; OS: Mac OS X 13.2.1; UI render: default; VCL: osx Locale: nl-BE (nl_BE.UTF-8); UI: en-US Calc: threaded
The following tip might help, but it may also need some adjustment. Introducing a static value for DATE and/or TIME can (potentially) be achieved by adequate keyboard shortcuts (when using the classic English USA QUERTY keyboard layout). Insert current date (static): [CTRL]+[;] Insert current time (static, i.e. timestamp): [CTRL]+[SHIFT][;] You can also: 1. Enter edit mode (F2) 2. [CTRL]+[;] 3. [SPACE] bar (for example) 4. [CTRL]+[SHIFT][;] 5. [ENTER] Introducing any of the above in one cell allows you to use it "statically", i.e. it will not get updated unless you change it manually. Then you can use that cell in other formulas. These keyboard shortcuts might not work with other keyboard layouts (bug?), but you can go to menu Tools > Customize > Keyboard and adapt it to your needs. Independently of the requested functions, the correct functionality of the mentioned keyboard shortcuts (or alternative ones) for non-QUERTY(USA) layouts / languages should be checked and corrected if necessary (because it does not always work as users expect).
Why do you link 154137 to 154136 ? These are two different things! 154136 is about not working shortcuts on an AZERTY keyboard, and 154137 is a request to have two new functions.
But such non-volatile functions don't avoid to change with a calculation [F9] or a hard recacl [Ctrl+Shift+F9]
> But such non-volatile functions don't avoid to change with a calculation [F9] or a hard recacl [Ctrl+Shift+F9] -> don't avoid to change? -> Yes, it does! because I tested it with function RAND.NV(). Do these steps: 1. in cell B1 enter: =IF(A1="Blabla";RAND.NV();"") 2. In cell A1 enter "Blabla" 3. In an other cell enter something else OR when an other cell is selected, do a recalculation. Result: the value in cell B1 doesn't change anymore!
(In reply to Alexander Van den Panhuysen from comment #4) > 3. In an other cell enter something else OR when an other cell is selected, > do a recalculation. > Result: the value in cell B1 doesn't change anymore! Let's clarify: _ Any function recalculates according to the Calculate settings (or it should). _ function.NV() recalculates as any other non-volatile function. This means that, for example: 1. New Calc. 2. B1: =IF(A1=1;NOW();"") 3. A1: 1 ...will show you the serial number for NOW() in cell B1, and then... 4. While in A2, pressing [F9] will not recalculate B1. 5. While in A2, pressing [CTRL]+[SHIFT]+[F9] _will_ recalculate B1. For MacOS, that would be [CMD]+[SHIFT]+[F9]. If you need to _never_ recalculate, that would be a static value (no need for a function in that case). If there is another trigger that would force to update the value, that would not be a completely static value. Whether the requested functions may or may not be useful, that's another matter.
I have read somewhere that RAND.NV() is made just to avoid to change a second time. In my example with RAND.NV(), it is exactly doing what I want, that is not the case with function NOW() nor TODAY(). Furthermore, why should I press F9 to recalculate my Calc sheet?, I am entering peaces of texts in A2, A3, A4, and so on -> then a recalculation occurs each time and the value in B1 remains the same. So, I want that a function like NOW works just like RAND.NV().
(In reply to Alexander Van den Panhuysen from comment #6) > So, I want that a function like NOW works just like RAND.NV(). I was just clarifying the facts. It is still not clear (to me, from your request in comment 0) whether you intend for the first values to ever be recalculated under any circumstances. If the initial value has to be completely static (i.e. not even recalculate with [CTRL]+[SHIFT]+[F9]), then you don't really need any function but rather a static value, and you already know how to introduce that. Let's leave this ticket to the assessment about whether the requested functions would/could potentially be implemented. For further general questions, please use: https://ask.libreoffice.org
Hallo That is a contradiction in itself, to create absolute non_volatile TODAY- or NOW-functions! and yes there are shortcuts to insert fixed Date|DateTime|Time
Mr. Tietz, how do you mean with "That is a contradiction in itself ..."? To me, it is nothing more then logic that these functions exists. And what are you saying about 'shortcuts'? These shortcuts are not working with a non-QUERTY(USA) keyboard. Read first everything of 154137 before replaying something useless.
(In reply to m.a.riosv from comment #3) > But such non-volatile functions don't avoid to change with a calculation > [F9] or a hard recacl [Ctrl+Shift+F9] Or when reloading the document and the formula expression is recalculated for whatever reason. So the intention to have a _timestamp_ of some event can not be fulfilled. (In reply to Alexander Van den Panhuysen from comment #6) > I have read somewhere that RAND.NV() is made just to avoid to change a > second time. No. It does not recalculate on every change of spreadsheet content (as RAND() does), but is not guaranteed to not recalculate another time with a different result.
Dear Rathke, you are saying "..., but is not guaranteed to not recalculate another time with a different result." When is that going to happen then? I tested the formula with function RAND.NV() even further: I saved the Calc sheet, closed it and after some seconds reopened that same sheet again -> I still see the same value in cell B1. And as I have written in comment 6, I don't use function key F9 at all, I don't even think of that key. It brings me to the question: "What is the purpose of the existing functions NOW() and TODAY() in a Calc sheet as the resulted values changes al the time?"
(In reply to Alexander Van den Panhuysen from comment #11) > When is that going to happen then? Whenever needed or as a side effect when an expression is recalculated that happens to also contain RAND.NV(), like IF(TODAY()>A1;RAND.NV()) where A1 contains a date smaller than today the RAND.NV() is still recalculated on every sheet content change. > It brings me to the question: "What is the purpose of the existing functions > NOW() and TODAY() in a Calc sheet as the resulted values changes al the > time?" Obviously with TODAY() one can calculate the current days since a given date or until a given date or determine a calendar date.
Your formula IF(TODAY()>A1;RAND.NV()) surprises me, so the result is unstable since your not allowed anymore to enter something in an other cell. But with my formula IF(A1="Blabla";RAND.NV();""), it works as I wanted. If function NOW.NV() will not be made, how can I get a fixed timestamp when a product or person has arrived?, because that is exactly wat my formula would accomplish.
(In reply to Alexander Van den Panhuysen from comment #13) > Your formula IF(TODAY()>A1;RAND.NV()) surprises me, so the result is > unstable Nothing surprising. The expression is volatile because it uses a volatile function, here TODAY(). > But with my formula IF(A1="Blabla";RAND.NV();""), it works as I wanted. Only as long as the expression is not recalculated for whatever reason. Which is out of the control of the user. Or rather, users can force recalculation with F9 (partial) or Shift+Ctrl+F9 (full) or on load of the document, but not prevent or even forbid it. > If function NOW.NV() will not be made, how can I get a fixed timestamp when > a product or person has arrived?, because that is exactly wat my formula > would accomplish. By using timestamp _data_. Either by the (unfortunately for your system not working, bug 154136) shortcuts, or a macro run (maybe triggered by an event or a button) that adds data according to conditions.
Damned people! My formula IS OK !! Even when someone presses F9 !!
You think so? You'd have to try it on the cell with your formula, or Shift+Ctrl+F9.
First: You keep on talking about the use of ... Ctrl ..., on an Apple computer it is Command ! Secondly: selecting the cell where my cell is? Why on earth would I do that? Not when the formula is ok. Even more: I protect the cells were I have entered a formula and unprotect only the cell(s) where I want to enter data. So the sheet is protected with a password.
Protecting sheets/cells does not prevent them from being recalculated. And if your Apple names it Command, all others name it Ctrl and I won't write "Ctrl or Command"+ whenever talking about.. And why on earth you or someone else would or would not hit F9 on whatever cell I don't know, fact is there is no guarantee that a formula is not recalculated and produces a different result if it contains a function that depends on datetime or other changing criteria. I'm tired of this discussion.
I am also tired of all this, but as I read well, a solution for bug 154136 is on the way.