Make the result of NOW() function persistent

[kdev]

I just notice that when I close the spreadsheet and reopen it, it recalculates and returns the current date and time.
Is there any way to make the result persistent?

Thanks

[anon73440385]

I just notice that when I close the spreadsheet and reopen it, it recalculates

It recalculates on every change within the sheet. NOW() is one of the so-called “volatile” spreadsheet functions.

[sokol92]

It seems to me, more precisely, that the volatile function is recalculated every time any sheet of this document is changed (by the way, in Excel: every time any sheet of any open workbook is changed).

[eeigor]

All about “volatile”…

http://www.decisionmodels.com/calcsecretsi.htm


See volatileFuncs.zip (comment Debug Statement if not Win OS)


So you can test any formula.

[anon73440385]

Just to add some information relevant to LibreOffice: Other volatile functions (besides RAND and RANDBETWEEN)

[eeigor]

You’re making assumptions. But you can check with this file yourself. And by adding your own example, you can test something of your own… The fact that this is Excel should not serve as a restriction. Everything all right.

---

[VolatileFuncs.xls|attachment](upload://z5Eu3Go9lI3igWFeBTLDqhujhhx.xls)

[anon73440385]

You’re making assumptions.

Yes - I make one and only one: This site is about LibreOffice and not Excel.

[eeigor]

VolatileFuncs.ods


Press F9

Unfortunately, the absence of an Intermediate window (as in VBA) does not allow you to output formulas. I do not know how to replace the line:
Debug.Print Application.Caller.Formula

[anon73440385]

Hello,

turn it to a value through Data -> Calculate -> Formula to Value

However

If you want current date and time from the very beginning as a fixed value, you should not use TODAY() or NOW() but Insert -Date and/or Insert-> Time or their respective shortcuts CTRL+, and CTRL+SHITFT+; (may require some redefinition, based on your locale keyboard layout)

[kdev]

CTRL+SHITFT+; works like a charm! Many thanks!

[JohnSUN]

Yes, this can be done in several different ways; a lot of such methods have been invented.

This is a formula like as =IF(B2<>"";B2;IF(A2="";"";NOW ())) with iterations enabled (don’t try, today it will not work), and a simple macro that inserts result of function =NOW() into a cell as value (@Zizi64, do you remember this post?), and complex macro which will insert timestamp to cell by some conditions or events.

However, you should not make life difficult for yourself. Just press Ctrl+Semicolon and Ctrl+Shift+Colon or choose this commands from menu

(@anon73440385 shoots faster )