How to Insert Current Date in Google Sheets

google sheetsgoogle-sheets-timestamp

I've got a spreadsheet in Google Drive and one column is meant for the date the entry is made. Is there a button to do this? I'm aware of the functions =NOW() and =TODAY() but their description says "the value is updated when the document recalculates". However I reopened the document a few minutes latter and it still had the same time. Why is this?

It would be even easier that when I start typing in a new row the column for the date auto-populates.

Best Answer

I was able to reproduce your findings, however, opening a spreadsheet doesn't result in a re-calculation.

Press Ctrl + R to recalculate the entire sheet.

There's one concern about this method: it changes all NOW() formula's into the current date/time.
Therefore you might want to consider pressing Ctrl + ; in the creation date cell and add the date manually.

Reference: Recalculation of Sheet

Related Solutions

Google-sheets – Add a ‘creation date’ value when a new line is edited

You could add a trigger that runs when new rows are added to the spreadsheet:

function onModification(event) {
  var sheet = event.source.getActiveSheet();
  if (event.changeType == "EDIT") { // A cell is edited. Other values include "INSERT_ROW" (when a new row is inserted)
    fillEmptyRows(sheet);
  }
}

function fillEmptyRows(sheet) {
    var range = sheet.getRange(1, 1, sheet.getMaxRows(), 1);
    var firstColumnValues = range.getValues();
    for (var i = 0; i < firstColumnValues.length; i++) {
      if (firstColumnValues[i][0] == "") { // Column 0 is A
         firstColumnValues[i][0] = new Date();
      }
    }
    range.setValues(firstColumnValues);
}

You need to register this trigger manually: Click Resources -> Triggers -> Add new trigger, select the onModification method and select on modification from the right-most dropdown menu.

When a cell is edited, the script will fill all blank cells in column A with the current timestamp.

I have set up an example spreadsheet, feel free to play with it.

Google Sheets – Automatically Fill Missing Values

I added this formula to your spreadsheet

=ArrayFormula(if(row(B2:B) <= max(if(not(isblank(B2:B)), row(B2:B))),vlookup(row(B2:B),filter({row(B2:B),B2:B},len(B2:B)),2),))

It seems to deliver what you asked, except for the interpolation part. Enter this formula in row 2, no need to drag down. I hope this helps ?

Best Answer

Related Solutions

Google-sheets – Add a ‘creation date’ value when a new line is edited

Google Sheets – Automatically Fill Missing Values

Related Topic