We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Make the result of NOW() function persistent

asked 2021-06-09 09:58:03 +0200

kdev gravatar image

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?


edit retag flag offensive close merge delete


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.

Opaque gravatar imageOpaque ( 2021-06-09 10:23:43 +0200 )edit

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).

sokol92 gravatar imagesokol92 ( 2021-06-09 12:42:54 +0200 )edit

All about "volatile"...
See volatileFuncs.zip (comment Debug Statement if not Win OS)
So you can test any formula.

eeigor gravatar imageeeigor ( 2021-06-09 19:05:29 +0200 )edit
Opaque gravatar imageOpaque ( 2021-06-09 19:31:12 +0200 )edit

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.


eeigor gravatar imageeeigor ( 2021-06-09 19:47:55 +0200 )edit

You're making assumptions.

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

Opaque gravatar imageOpaque ( 2021-06-09 20:10:25 +0200 )edit

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

eeigor gravatar imageeeigor ( 2021-06-09 22:01:03 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2021-06-09 10:21:05 +0200

Opaque gravatar image

updated 2021-06-10 11:54:55 +0200


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


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)

edit flag offensive delete link more


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

kdev gravatar imagekdev ( 2021-06-10 11:43:08 +0200 )edit

answered 2021-06-09 10:34:34 +0200

JohnSUN gravatar image

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


(@Opaque shoots faster :-) )

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-06-09 09:58:03 +0200

Seen: 42 times

Last updated: Jun 10