On 18/05/2009 16:05, First Last wrote:
Thanks, Harold. I appreciate that you have considered my request to be worth
much of your time :-) .
I'm pretty certain that the recursive formula which you/Winfried Rohr have offered is a formula that will recalculate when I press F9. I believe that when I made my first post I mentioned that such a solution would not be acceptable: "...nor do I want any other function which will change when F9 is pressed."
It doesn't.
The only time it would change is if the contents in A1 are set to
blank/null, at which point the timestamp goes null. The timestamp then
attains a new "now" value if A1 is again set non-null. In other words,
provided A1 stays non-null, B1 (the timestamp) stays constant. Also, the
timestamp does not change if A1 changes from one non-null value to
another "in one go" i.e. *not* via an intermediate null value.
You could have a spare cell - ZZ999 for example - which is *permanently
non-null*. The formula could refer to it. Any cell into which that
formula is copied will attain a "now" value which will never change
provided ZZ999 doesn't go null.
Try it. I have.
Further, I titled my post with the words "insert a date string into a cell".
Use the extension; assign a key to it ("zeitfeld" = literally "time
field") as described in the discussion link I sent. When you press the
key you'll get a *text string* representing the time when you press the
key. That string will not change unless you *explicitly* change it. In
particular it will not change on F9.
The reason for this limitation is this: I will use in spreadsheets whatever solution is successful,
Errm. No, I don't think you will. There are two solutions that work -
the extension and the formula - but you clearly haven't tried either.
those current and future spreadsheets may need to have several cells
calculated; why generate a spreadsheet in a condition where certain cells must
be calculated yet the cells with date strings must NOT be calculated?
If you meant that date strings must not be *re-*calculated, the
extension offers a text string which simply doesn't recalculate.
The formula does actually recalculate but the answer remains constant.
The distinction is obvious but irrelevant.
That would represent a major hurdle, in my opinion.
What sort of hurdle does it represent?
One of the major benefits of OpenOffice over, say, MS Office, is its
system of extensions by which individual users like Winfried can add
functionality they think is necessary/desirable without having to wait
years for some vast bureaucracy to take value/resource based decisions
about what to implement.
--- [email protected] wrote:
From: Harold Fuchs <[email protected]>
To: [email protected], [email protected]
Subject: Re: [users] Calc: insert a date string into a cell - not a function,
not a format.
Date: Mon, 18 May 2009 10:06:42 +0100
2009/5/14 Harold Fuchs <[email protected]>
2009/5/14 First Last <[email protected]>
I want to insert a string into a cell using keystrokes. I'm sorry this will
seem overdone, but I want people to know I've tried to research this on my
own.
I'm using Open Office 3.0-r14320, I use PCLinuxOS and this version of Open
Office came from the PCLinuxOS repository. For some reason, there are no
Open Office help files installed (in Calc, F1 creates error).
The string I want to insert is the current date, this is a static number.
I don't want the date() function, nor do I want the time() function, nor
do I want any other function which will change when F9 is pressed. I also do
not wish to 'work around' by pasting a function then later copy and paste
the value of the cell over the function. I do not want to use nor do I want
to create a 'Macro'. I'm not asking 'how do I change the format of the
current (or any other) cell'.
What I want is to do is insert into the active cell a string which
represents 'now', now means 'this exact moment in time' (well, 'now'
according to system clock). The value of the string is static, because it
represents the numerical value of the ONE moment when the insert keys were
pressed. F9 won't change it, recalculation won't change it. Directly editing
the cell CAN change it. Maybe it will look like this: 38755.1204, and a few
seconds later, like this: 38755.1911. I think that is the format of the Open
Office Calc internal date string.
I would like an action similar to Excel 97 where I press a modifier key
and FOR EXAMPLE the double quote (") key and poof, a number which represents
this moment in time, right now, is inserted into the current cell.
To help you get what I mean, current descriptions on the Microsoft Excel
website describe the keys as "ctrl +", but I stopped using Excel .... I hope
you do not wonder why.
I'll select a date format for the the cell outside of this request
(usually I format the whole column to a specific date format, I'm pretty
sure I'm still capable of that much, but I have my senior moments).
I've searched the mailing list archives for 'date string', but after 45
minutes of reading, the list of what I've found seems to have no answer.
Examples that aren't what I want:
http://www.openoffice.org/servlets/ReadMsg?listName=users&msgNo=136052
results in a discussion for an older version of OO: "It's in the menu
INSERT->FIELDS->DATE"
Insert menu still exists, but 'fields' is not there any more.
http://www.openoffice.org/servlets/ReadMsg?listName=users&msgNo=180920
results in discussing how to format a cell with a string.
http://www.openoffice.org/servlets/ReadMsg?listName=users&msgNo=191309
also discusses INSERT->FIELDS->DATE as well as discussing formatting
fields
I realized that since message 191309 is a message that was posted on 18
Jan 09, I'd have to refer to posts more recent than 18 Jan 09.
One post that I found (
http://www.openoffice.org/servlets/ReadMsg?listName=users&msgNo=195082)
contains a VERY near miss:
o Go to Tools | Customize... | Keyboard.
o At the top right, select the appropriate radio
button - for OpenOffice.org or for the relevant component.
o Under Category, select Insert.
o Under Function, select Date.
o Under "Shortcut keys", select your preferred keystroke.
o Click Modify and OK.
Under the Function category, I do not see an item called "Date". I wish it
were there!
I must be just a few fractions of a revision behind the 8 ball.....
Any ideas?
TIA.
There's an extension that I think fits the bill.It's at <
http://extensions.services.openoffice.org/taxonomy/term/70>. It's written
by/for a German user so you need to tinker with the date formats it
produces. There's a discussion of how to do this, and of how to use the
extension, at <http://www.oooforum.org/forum/viewtopic.phtml?t=74711>.
Hmm. I'm not sure if it will work in OOo 3.x; it works in my 2.4.1. You may
need to dink the "version value" field in the extension. To do this, ensure
you save the extension prior to installing it. Open the downloaded/saved
.oxt file with whatever is your equivalent of WinZip (gZip???). You'll see
several filenames. Open the one named "description.xml" in a text editor.
You'll see a tag '<version value="2.0.3"/>'. Change the "2.0.3"
appropriately and save the changed "description.xml" back into the .oxt
archive. Now install the extension.
Hmm again. The extension's version value is 2.0.3 but it ran happily in my
2.4.1 so perhaps the version value field is ignored. You might want to try
installing/running the extension as is and only if it doesn't install/run
properly change the version value field as described above.
Sorry to reply to my own post but the simple formula
=IF(A1<>"";IF(B1<>"";B1;NOW());"") entered into cell B1 puts a non-changing
timestamp in B1 if A1 is/becomes not empty but sets B1 empty if A1
is/becomes empty. Sorry but, so far, I can't get B1 to change if and only if
A1 changes.
Oh. To make this work you need to check the "Iterations" box at
Tools>Options>OpenOffice.org Calc>Calculate. If you don't do this then B1
will show Err 522 - Circular Reference - because the formula in B1 refers to
B1.
Thanks to Winfried Rohr, author of the above mentioned extension, for this
extra goody.
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
