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." Further, I titled my post with the words "insert a date string into a cell". The reason for this limitation is this: I will use in spreadsheets whatever solution is successful, 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? That would represent a major hurdle, in my opinion. --- [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] _____________________________________________________________ Are you a Techie? Get Your Free Tech Email Address Now! Visit http://www.TechEmail.com --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
