How to stop NOW function from auto-updating?

Question

I'm using Google Spreadsheet's NOW() function as a shortcut to insert the current timestamp now.

However, the value in the cell changes whenever the spreadsheet is modified in any way, even when the changes have nothing to do with that particular cell.

How do we stop the value in the cell from being automatically updated?

Answer 1

Try Shift+Ctrl+: and Ctrl+;

Those are the keyboard shortcuts for "insert time" and "insert date" respectively.

(based on pnuts' comment)

Answer 2

This is an old post, but I was looking into this recently and figured I'd chime in.

Depending on you needs, the easiest answer I could think of is by creating a drop down menu and referencing a function on another cell.

Example:

1. Select A10:A20 > right click > Data Validation
   • Criteria = "List from range" > F2
2. Formula in cell F2: =mult(now(),1)

To insert a time and date into a data validated cell (A10:A20 in this example), click on the drop down arrow and click on the time listed. This will input the date and time into the field and not change it each time the spreadsheet is updated. If you'd like to change the formatting of the cell to be just time, or just the date, it can be done easily (select desired cell(s) > Format > Number > select format).

Answer 3

The documentation says this:

The NOW Function and Worksheet Recalculation The NOW() function is a member of Google Sheet's group of volatile functions, which, by default, recalculate or update every time the worksheet in which they are located recalculates.

For example, worksheets recalculate each time they are opened or when certain events occur — such as when data is entered or changed in the worksheet — so if the date or time is entered using the NOW() function, it continues to update.

The spreadsheet settings, which are located under the File menu in Google Sheets, offers two additional settings for when a worksheet recalculates:

On change and every minute On change and every hour There is no option within the program for turning off recalculation of volatile functions.

Keeping Dates and Times Static If having the date or time continually change is not desirable, use a shortcut option for entering static dates and time include typing the date or time manually or entering them using the following keyboard shortcuts:

The static date shortcut is Ctrl+; (semi-colon key) The static time shortcut is Ctrl+Shift+: (colon key)

but I would consider using the function TIMESTAMP just like this:

=TIMESTAMP()

This would give you static date time as follows

8-15-2019 16:53:52

But there is no function such as TIMESTAMP(). You can easily add this: Go to tools>Script Editor and paste the following code

function TIMESTAMP() {

  var today = new Date();
  var date = (today.getMonth()+1)+'-'+today.getDate()+'-'+today.getFullYear();
  var time = today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds();
  var dateTime = date+' '+time;
  return dateTime;

}

Save the script and your sheets are now ready to use this function.

Answer 4

Another option is to copy the value of the cell and paste it in the same respective cell, but with values only special paste option.

• Input =NOW() in a cell.
• Copy it Ctrl+C
• Paste in the same cell Ctrl+Shift+V

Answer 5

Another option (an expansion of this answer ... "Example: 1. Select A10:A20 > right click > Data Validation Criteria = "List from range" > F2 2. Formula in cell F2: =mult(now(),1) " )

Is to create two cells, one with a tick box option (A3), and the one next to it (B3) with the "list from range" validation.

If you do that, then whenever you tick the box in A3, the formula in cell F2 will refresh, so the dropdown in B3 will have a data validation with the current time, which you then select. Unless you reselect that dropdown at a later time, the content of B3 will not change.

I am planning to use this to mark an "attendance register" with the time each attendee turned up to a meeting. This will be much better than my current method, which is to put the "now" formula into a cell (e.g. B3) which triggers when the check box next to it (e.g. A3) is ticked, then run a macro to convert the time each attendee turned up from a dynamic formula to text (so it doesn't change to the new "now" when the "current time" changes.

Answer 6

I tried creating the timestamp() function with the above script but it still updates for me the same way Now() does. I think the only reliable option is the keystroke.

Answer 7

automatic re-calculations of volatile functions like =NOW() =TODAY() =RAND() =RANDBETWEEN() in google spreadsheet can be frozen (for 30 minutes)

here is a small example for generating two "frozen" keys implementing RANDBETWEEN:

1. create new spreadsheed and name it "KEY1"
2. set values for range A1:P1 like:

3. insert this in cell A3:

   =index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))

4. create a copy/duplicate of whole spreadsheet and name it "KEY2"

5. create new (3rd) spreadsheet and name it "ALL_KEYS"

6. enable sharing for all 3 spreadsheets and in advanced options select "Can edit" (please note that with not doing this step all will result in #REF error because those spreadsheets needs to be linked to each other)

7. in this 3rd spreadsheet set cell A1 and A2 as follows:

   =IMPORTRANGE("paste-here-whole-url-of-KEY1-spreadsheet";"Sheet1!$A$3") =IMPORTRANGE("paste-here-whole-url-of-KEY2-spreadsheet";"Sheet1!$A$3")

8. create new (4th) spreadsheet and name it as you wish (and also enable sharing)

9. insert each formula in any cell (or even any Sheet tab across whole spreadsheet) example:

   D3 =IMPORTRANGE("paste-here-whole-url-of-ALL_KEYS-spreadsheet";"Sheet1!$A$1") D4 =IMPORTRANGE("paste-here-whole-url-of-ALL_KEYS-spreadsheet";"Sheet1!$A$2")

10. right click on cell C3 and select "Data validation..." then as Criteria select "Checkbox" and check "Use custom cell values". then as TRUE input number 1 and as FALSE input number 0

11. do same for C4 cell (this will work as on/on switch rather than on/off)

12. now go to spreadsheets "KEY1" and "KEY2", and in both of them insert this into A2 cell:

    "KEY1" -> A2 =IMPORTRANGE("paste-here-whole-url-of-4th-spreadsheet";"Sheet1!$C$3") "KEY2" -> A2 =IMPORTRANGE("paste-here-whole-url-of-4th-spreadsheet";"Sheet1!$C$4")

13. close spreadsheets "KEY1", "KEY2" & "ALL_KEYS", and never open them again

14. done! bonus step: to make sure that those two randomly generated keys are unique you can add alert like =IF($A$1<>$A$2;"unique";"reload") in "ALL_KEYS" spreadsheet, and then import it into 4th spreadsheet like =IMPORTRANGE("paste-here-whole-url-of-ALL_KEYS-spreadsheet"; "Sheet1!a10")

SUM: now whenever you enable/disable your "switch" it will generate random fresh key for you which will stay there until next pushing of the switch (not even RELOAD of browser tab will change those randomly generated keys). as you can see there is a slight offset key1 in "KEY1" and key1 in "ALL_KEYS" & "4th", but that does not merit anyhow...