Is there a way to add the time to a cell automatically so it doesn’t update?
Essentially, I have 2 columns. ColA will contain the times that a cell in ColB is filled in. I want successive times as I enter data in the B cells. I don’t know how to enter the value NOW() converted to text so as I enter data in cell B4, all the times in A1-A4 update.
I did try the formula =IF(B1<>"",IF(A1="",NOW(),A1),"") as presented in the documentation but I get Err:523 rather than a time.
I did try the formula =IF(B1<>"",IF(A1="",NOW(),A1),"") as presented in the documentation
Please mention the documentation you refer to; it apparently is misleading, and should be corrected.
Hello,
you can’t do that by any formula, since formulas are recalculated. There is no formula having a some kind of a “only calculate, when called for the first time”-option. If you want fixed times, you may use NOW() but afterwards you need to perform Data -> Calculate -> Formula to Value- conversion.
Regarding error 523: Obviously you put the formula into a cell (probably cell A1) which causes a circular reference and there is no covergence (of course not, since NOW() seems just to be recalculated 100 times) after the number of iterations defined in Tools -> Options -> LibreOffice Calc -> Calculate -> [x] Iterations (which by default is unset and if set, option Steps: is 100 by default). See also LibreOffice Help - Error Codes.
Incidental remark: You can use CTRL+SHIFT+; to add the current time via keyboard shortcut.
Hope that helps, though you cannot achieve your goal.
If the answer helped you to solve your problem, please click the check mark (
) next to the answer.
though you cannot achieve your goal
That sounded like some “missing functionality”, when in fact it’s a misconception on OP part. Formulas are used to dynamically provide values based on some current state (e.g., changed data in other cells; changed variables; etc.); to have static data, it should be created static. Of course, one could want to have it automatic; but automatic not the same as dynamic: for automatic entry of some static data, you might use macros (including attached to sheet events); or keyboard shortcut (to have semi-automatic entry - like using Insert->Date). The code executed at the moment of entry would calculate the data, and enter it statically.
though you cannot achieve your goal.
I was coming to that conclusion. I was hoping an expert might know something I didn’t. As it was, you experts explained why not. I did find the CTRL-SHFT-; key in my searching. That can suffice well enough for my purpose.
Regarding error 523: Obviously you put the formula into a cell (probably cell A1) which causes a circular reference and there is no covergence (of course not, since NOW() seems just to be recalculated 100 times) after the number of iterations defined in Tools → Options → LibreOffice Calc → Calculate → [x] Iterations (which by default is unset and if set, option Steps: is 100 by default). See also LibreOffice Help - Error Codes.
Yes, I followed the instructions exactly, including setting iterations to 1. The instructions were at Frequently asked questions - Calc - The Document Foundation Wiki. As far as the Error codes, it explained just enough for me to comment “well, that’s clear as mud.” The error states:
Statistical (financial) functions tend towards a given value [what value? 0? inf? 3.1416?] but do not reach it, or iteration of references in a loop [set to 1] do not reach the minimum modification [meaning?] within the defined maximum increments.
Can’t decipher what it’s trying to tell me. Especially in this instance. I’m not a statistics expert.
It looks like that formula posted eventually gets a correct value – eventually. I came back after a while and there were values in those cells. Just tested it again and resizing a column (any column) removed the error and inserted the expected time. Weird!