24

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

10 Answers 10

19

Try Shift+Ctrl+: and Ctrl+;

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

(based on pnuts' comment)

7
11

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
  • 3
    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. Apr 12, 2016 at 11:03
7

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()} 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, 2022 at 13:28
2
=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.

2
  • That is the one I was looking for! I suppose this is the correct answer! Jan 30, 2023 at 10:20
  • This is the solution I want to work, but I keep getting updates on mobile. Sometimes, it will lock in the "now" and other times it will update the most recent entries when I reopen the sheet and I can't figure out why.
    – goodguy5
    Oct 26, 2023 at 4:37
2

Per, A way for static "now" in Google sheet? (@the-god-of-biscuits), putting a volatile function inside a Lambda function makes it 'sticky'.

For example, if the lambda contains no external references, the datevalue first returned will not update unless the formula's cell is edited.

If the Lambda contains a reference, the volatile function will update if the referenced range is edited.

  1. Sticky
    Value first returned will 'stick' until the formula's cell is edited.

    =lambda(x;x)(now())
    
  2. Less Sticky
    Value first returned will 'stick' until the formula's cell, or the referenced range, is edited.

    =lambda(x;y;x)(now();A1)
    
  3. Volatile

    =now()
    

I use this approach to insert current dates, as it is the simplest solution I've found to make a volatile function behave like it's non-volatile.

3
  • This works however is this a solution in search of a problem? What I mean is, what is the use case where explicitly baking in risk that your preferred value will change become more attractive than one that guarantees your value will remains static, such as using a string like in the accepted answer?
    – Blindspots
    Dec 9, 2023 at 16:22
  • 1
    This is the method that I needed. While keyboard shortcuts are great, I needed the spreadsheet to enter the date conditionally. So, I can put this very simple lambda function solution inside an IF function. There's no way to do that with keyboard shortcuts. Dec 10, 2023 at 17:51
  • Good example, and worth adding the use case to your answer for the benefit of future visitors. An example of the "2. Less Sticky" above where every time the cell(s) referenced by the IF is edited, regardless if it changes the true/false state or not, the NOW function will update.
    – Blindspots
    Dec 10, 2023 at 18:10
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...

-1

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. May 18, 2022 at 19:54
  • The most usefull in my opinion shortcut for inserting current date and time is absent there
    – Slava
    May 19, 2022 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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