21

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?

3

9 Answers 9

19

Try Shift+Ctrl+: and Ctrl+;

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

(based on pnuts' comment)

7
  • 3
    Some shortcuts, including date and time stamp, doesn't work for some international keyboard layouts for the Google Editors (Docs, Drawings, Sheets, Slides). It's a "known" issue. Later I'll pass some references.
    – Rubén
    May 25, 2015 at 16:47
  • I expect there are probably localized keyboard shortcuts for other keyboard layouts.
    – ale
    May 25, 2015 at 18:14
  • I expect the same but I didn't found them yet.
    – Rubén
    May 25, 2015 at 18:23
  • Not working on firefox linux :-( is there a way to get this function through menus??!! @Rubén seems you forgot the references ;-) May 11, 2018 at 11:59
  • 1
    Anyway, check out this Google drive keyboard shortcuts not working with non-US keyboard
    – Rubén
    May 11, 2018 at 14:43
10

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

1
  • 2
    I didn't figured out this solution. Thank for sharing it. It's worth to say that using =NOW() in List from range also works by the other side it's very likely that other non-deterministic functions like TODAY(), RAND() and RANDBETWEEN could be used too.
    – Rubén
    Apr 12, 2016 at 11:03
8

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.

3
  • Welcome. Custom functions are recalculated every time that a spreadsheet is opened and when their arguments change. By the way a simple version is function TIMESTAMP(){ return new Date()}
    – Rubén
    Aug 15, 2019 at 12:50
  • is there no way to do this other than inserting it manually? Aug 22, 2019 at 6:14
  • this one works for me, when using a formula Jul 28, 2020 at 3:36
2

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
1
  • which is the same as stated in one of the links in previous comments
    – 47tucanae
    Nov 23, 2019 at 12:22
2

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.

1
  • This works really well. The only negative, as with the earlier answer, is that the cells with the recorded time, like B3, are flagged as "invalid" by the data validation, once the data validation range is next updated. But perhaps not a big issue in most cases - just cosmetic.
    – kirkg13
    Jan 27 at 13:28
0
=IF(A1<>"",IF(B1<>"",B1,NOW()),"")

This will provide the date/time in B1 as soon as cell A1 is populated, and it will not update as the sheet updates. You will have to turn on iterative calculations though.

0

This script by --Hyde is amazing. I stared at it for a while and then decided to just try it.

It is JavaScript code that looks for any edits to a particular column (and within a specified range of rows) and if there are modifications, then it saves the current timestamp to another column.

There is no need to call a special function in your Google Sheet once you save the Code.gs file.

Copy the entire code:

'use strict';

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet.'
    );
  }
  yetAnotherTimestamp_(e);
}
 

/**
* Inserts a timestamp when a watchColumn is edited.
*
* @param {Object} e The onEdit() event object.
*/
function yetAnotherTimestamp_(e) {
  // version 1.2, written by --Hyde, 8 May 2022
  //  - add valueDeleted, overwriteTimestamp, eraseTimestampOnDelete
  //  - see https://support.google.com/docs/thread/162786302
  // version 1.1, written by --Hyde, 24 March 2022
  //  - use regexes in sheetName
  //  - see https://support.google.com/docs/thread/156975294
  // version 1.0, written by --Hyde, 13 November 2021
  //  - see https://support.google.com/docs/thread/135269995
  const parameters = [
 
    ////////////////////////////////
    // [START modifiable parameters]
    {
      sheetName: /^(Sheet1|Sheet2|Sheet3)$/i, // this is a regular expression; use /./i to match all sheets
      watchColumn: ['C', 'M', 'X'],
      stampColumn: ['D', 'N', 'Y'],
      watchRowStart: 2,
      watchRowEnd: +Infinity,
      timestampFormat: 'yyyy-MM-dd HH:mm',
      overwriteTimestamp: true,
      eraseTimestampOnDelete: false,
    },
    {
      sheetName: /^(Reply Form)$/i,
      watchColumn: ['B', 'E'],
      stampColumn: ['A', 'J'],
      watchRowStart: 9,
      watchRowEnd: +Infinity,
      timestampFormat: 'M/d/yyyy HH:mm:ss',
      overwriteTimestamp: true,
      eraseTimestampOnDelete: true,
    },
    {
      sheetName: /^(MASTER DO NOT CHANGE OR DELETE)$/i,
      watchColumn: ['E', 'G'],
      stampColumn: ['C', 'H'],
      watchRowStart: 2,
      watchRowEnd: +Infinity,
      timestampFormat: 'HH:mm AM/PM',
      overwriteTimestamp: true,
      eraseTimestampOnDelete: true,
    },
    {
      sheetName: /^(Pathway  3)$/i,
      watchColumn: ['N', 'O', 'Q', 'R'],
      stampColumn: ['P', 'P', 'S', 'S'],
      watchRowStart: 6,
      watchRowEnd: +Infinity,
      timestampFormat: 'yyyy-MM-dd HH:mm',
      overwriteTimestamp: true,
      eraseTimestampOnDelete: true,
    },
    // [END modifiable parameters]
    ////////////////////////////////
 
  ];
  let valueDeleted = false;
  if (!e.value) {
    if (e.range.rowStart === e.range.rowEnd && e.range.columnStart === e.range.columnEnd) {
      valueDeleted = true; // detect delete in a single cell
    } else {
      return;
    }
  }
  let sheetName, stampColumn;
  let timestampFormat, overwriteTimestamp, eraseTimestampOnDelete;
  const editColumn = columnNumbersToColumnLabels_(e.range.columnStart);
  parameters.some(par => {
    const watchIndex = par.watchColumn.indexOf(editColumn);
    if (watchIndex === -1
      || e.range.rowStart < par.watchRowStart
      || e.range.rowStart > par.watchRowEnd) {
      return false;
    }
    sheetName = sheetName || e.range.getSheet().getName();
    if (!sheetName.match(par.sheetName)) {
      return false;
    }
    stampColumn = columnLabelsToColumnNumbers_(par.stampColumn[watchIndex]);
    ({ timestampFormat, overwriteTimestamp, eraseTimestampOnDelete } = par);
    return true;
  });
  if (!stampColumn) {
    return;
  }
  let timestamp = null;
  switch (true) {
    case valueDeleted && !eraseTimestampOnDelete:
      return;
    case valueDeleted && eraseTimestampOnDelete:
      break;
    default:
      timestamp = new Date();
  }
  const timestampCell = e.source
    .getSheetByName(sheetName)
    .getRange(e.range.rowStart, stampColumn);
  if (!overwriteTimestamp && timestampCell.getValue()) {
    return;
  }
  timestampCell
    .setValue(timestamp)
    .setNumberFormat(timestampFormat);
}
 

/**
* Converts column numbers such as 1, 26, 27 and 18278 into column labels like "A", "Z", "AA" and "ZZZ".
*
* Returns a text string or an array of text strings that matches the dimensions of numbers.
* Supports single values and arrays like [1, 26, 27] or [[1, 2], [13, 14], [27, 28]].
* Returns null for '', null and undefined.
* Returns NaN for non-numbers and numbers less than 1.
*
* @param {Number[][]} numbers A column number or or an array of column numbers to convert to column labels.
* @return {String[][]} Column labels like "A", "Z", "AA" and "ZZZ", or NaN if numbers is not a positive number.
*/
function columnNumbersToColumnLabels_(numbers) {
  // version 1.7, written by --Hyde 3 November 2019
  if (Array.isArray(numbers)) {
    return numbers.map(columnNumbersToColumnLabels_);
  }
  if (numbers === '' || (numbers == null)) {
    return null;
  }
  if (typeof numbers !== 'number' || numbers < 1) {
    return NaN;
  }
  var alphabetStart = 'A'.charCodeAt(0);
  var alphabetSize = 'Z'.charCodeAt(0) - alphabetStart + 1;
  var colLabel = '';
  while (numbers > 0) {
    var colNumber = (numbers - 1) % alphabetSize;
    var asciiCode = colNumber + alphabetStart;
    colLabel = String.fromCharCode(asciiCode) + colLabel;
    numbers = (numbers - colNumber - 1) / alphabetSize;
  }
  return colLabel;
}
 

/**
* Converts textual column labels such as "A", "Z", "AA" and "ZZZ" into column numbers like 1, 26, 27 and 18278.
*
* Returns a number or an array of numbers that matches the dimensions of labels.
* Supports single values and arrays like ["A", "Z", "AA"] or [["A", "B"], ["M", "N"], ["AA", "AB"]].
* If the input array contains numbers or blank values, they are returned as is.
* Returns NaN for invalid labels such as "1A".
*
* @param {String[][]} labels A textual column label or an array of column labels to convert to column numbers.
* @return {Number[][]} Column numbers like 1, 26, 27 and 18278, or NaN if labels is not like 'A' or 'A1'.
*/
function columnLabelsToColumnNumbers_(labels) {
  // version 1.5, written by --Hyde 3 November 2019
  if (Array.isArray(labels)) {
    return labels.map(columnLabelsToColumnNumbers_);
  }
  if (typeof labels === 'number') {
    return labels;
  }
  if (labels === '' || (labels == null)) {
    return null;
  }
  var match = labels.toUpperCase().match(/^[A-Z]+/g);
  if (!match) {
    return NaN;
  }
  labels = match[0];
  var alphabetStart = 'A'.charCodeAt(0);
  var alphabetSize = 'Z'.charCodeAt(0) - alphabetStart + 1;
  var alphaValue = 0;
  var colNumber = 0;
  for (var i = 0; i < labels.length; i++) {
    alphaValue = labels.charCodeAt(i) - alphabetStart + 1;
    colNumber += alphaValue * Math.pow(alphabetSize, labels.length - i - 1);
  }
  return colNumber;
}

Replace Code.gs:

Go to Extensions > Apps Scripts...

Extensions > Apps Scripts

You probably have the default code block:

function myFunction() {
  
}

Replace what you have in Code.gs with the entire --Hyde code that you copied from above...

Change the const parameters definition as you need.

In my case, I wanted to fill column L with a timestamp any time that column A was populated, and I did not want the timestamp to get updated even if column A was updated (even if column A was erased and updated). To accomplish this, I set the const parameters definition as follows:

  const parameters = [
    {
      sheetName: 'Sheet1', // this is a regular expression; use /./i to match all sheets
      watchColumn: ['A'],
      stampColumn: ['L'],
      watchRowStart: 2,
      watchRowEnd: +Infinity,
      timestampFormat: 'yyyy-MM-dd HH:mm:ss',
      overwriteTimestamp: false,
      eraseTimestampOnDelete: false,
    }
  ];
-1

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: enter image description here
  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...

-2

You can use these shortcuts:

insert current date and time - Ctrl+Shift+Alt+:

insert current time - Ctrl+Shift+;

insert current date - Ctrl+;

2
  • 1
    The use of shortcuts was already mentioned in a top anwer.
    – Rubén
    May 18 at 19:54
  • The most usefull in my opinion shortcut for inserting current date and time is absent there
    – Slava
    May 19 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.