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

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