Easily Insert a Timestamp in Google Sheets

Google Sheets is an amazing tool, especially when collaborating and working with others on the same sheet. It allows people to work on the sheet simultaneously.

In this article, you will learn different ways to insert a timestamp in Google Sheets.

Consider Taking a Course

The basics of inserting a timestamp are fairly simple, but they can also become quite complex. It may be beneficial to take a course on the basics of Google Sheets if you still have difficulties after reading this article.

Insert a Timestamp Using Keyboard Shortcuts

If you want to manually insert timestamps at specific places in the spreadsheet, using keyboard shortcuts is the best solution.

Here are the keyboard shortcuts:

• To insert the current date: Control + ; (hold down the Control key and press the semicolon key).
• To insert the current time: Control + Shift + ; (hold down the Control and Shift keys and press the semicolon key).

For Mac users, you will use the following shortcuts:

• Command + ; (hold down the Control key and press the semicolon key) to insert the current date.
• Control + Shift + ; (hold down the Control and Shift keys and press the semicolon key) to insert the current time.

Insert Date and Time Using Functions

Google Sheets functions can be used to insert the current date or time.

Let’s take an example using our spreadsheet below:

You can find our example spreadsheet here.

To insert the current date in Google Sheets, enter the following formula in a cell:

=TODAY()

This function takes no input arguments.

If you want to insert both the date and current time, use the following function:

=NOW()

There are a few important points you should know about these functions:

1. The TODAY and NOW functions are volatile. This means that the formula is recalculated every time there is a change in the Google Sheets spreadsheet. For example, if you enter a value in a cell or close and reopen the sheet, it will recalculate and show you the date/time at the time of recalculation.
2. The result of the TODAY and NOW functions is a number. This means that you can change the format to display the date and time differently if needed.

Automatically Insert Date and Time Using a Script

The downside of using functions is that they recalculate and change the date/time value with every modification.

If you are tracking activities, you may not want it to change once it’s entered.

This can be done using a simple script in Google Sheets.

Here are the steps to follow:

1. Click on the “Tools” menu.
2. Click on the “Script editor” option.

In the script editor code window, copy and paste the following code (credit: Stackoverflow):

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { // check if we are on the right sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 1 ) { // check the column
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === "" ) // is it empty?
        nextCell.setValue(new Date());
    }
  }
}

• Save the code.

Now, when you enter something in the cells in column A, a timestamp will automatically appear in the adjacent cell in column B.

You can modify the column number in the script according to your document. For example, in our example spreadsheet below, we want the date to appear in the third column based on column B.

If you go back to your script, you will see the line for the column: if( r.getColumn() == 1 ) { // check the column.

Change the column number based on the column you want to use as a condition. In our case, it’s column B, so we will change the 1 to 2.

Just below that, we have the offset option: var nextCell = r.offset(0, 1).

This gives the cell where we want to insert the timestamp. The first number represents the row, and we set it to 0 because we want the timestamp to be in the same row as the data. The second number is the distance we want to move the date column relative to the condition column. You can choose the column where you want your timestamp to be.

In our case, we want it in column C, so we will change var offset to var nextCell = r.offset(0, 2).

This is because our condition is in column B, which is the second column, and we want the date to be in the third column, so we need to offset the column by 1.

Insert Timestamp Using IF and IF.MULTIPLE Functions

You can also use the IF function in Google Sheets to automatically insert a timestamp. Let’s take a look at our spreadsheet below.

We can make it so that when the task is completed and the status checkbox is checked, the date and time cell automatically gets filled with the time when the checkbox is checked.

To do this, we will use the IF function to create the right conditions.

In this case, we will use the following formula:

=IF(C2,C2,IF(B2=TRUE,NOW(),""))

For a checkbox, we will use B3=TRUE because checkboxes use the TRUE or FALSE operation. This means that if the checkbox is checked, then it’s True, so the formula will return the current date and time for “NOW”.

If we didn’t have checkboxes in our spreadsheet, we would use “Yes” instead of “True”.

Frequently Asked Questions

How can I create a timestamp when a cell is updated?

You can use the code mentioned above in this article to create automatic timestamps in Google Apps Script Editor. Then, you can modify the triggers in the left pane of the editor and add these details:

• Function: onEdit
• Deployment: Head
• Event Source: From spreadsheet
• Event Type: On edit

Then, click Save.

Conclusion

We hope this guide has answered all your questions on how to insert a timestamp in Google Sheets. While using the script may be a bit challenging for new users, you can often get by using just the keyboard shortcuts mentioned at the beginning of the article.

Find more tips and tutorials on Crawlan.com.