Easily Insert a Timestamp in Google Sheets

By

Google Sheets is a great tool when you’re collaborating and working with people on the same sheet. It allows people to work on the sheet simultaneously.

In this tutorial, you’ll learn various to insert a timestamp in Google Sheets:

Consider Taking a Course

The basics of inserting a timestamps are pretty straightforward. But, they are also capable of becoming very complex. It may be best to take a basics of Google Sheets course if you still have trouble after reading this article.

Insert a Timestamp in Google Sheets Using Keyboard Shortcuts

If you’re looking to manually insert timestamps in some places in the worksheet, using the keyboard shortcut is the way to go.

Here are the keyboard shortcuts:

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

For Mac, you’ll use the folllowing shortcuts:

  • Command + : (hold the Control key and press the colon key).
  • To insert current time: Control + Shift + : (hold the Control and Shift keys and press the colon key).

Insert Date and Time Using Functions

Google Sheet Functions can be used to insert the current date or time.

Let’s look at an example using our sheet below:

Example sheet for inserting timestamps

You can find our example sheet here.

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

=TODAY()

This function does not take any input arguments.

If you want to insert the current date as well as the time, use the below function:

=NOW()

There are a couple of important things you need to know about these functions:

  1. Both TODAY and NOW functions are volatile. This means that the formula gets recalculated whenever there is a change in the Google Sheet. For example, if you enter a value in a cell or close the sheet and open it again, it will recalculate and show you the date/time at the time of recalculation.
  2. The result of both TODAY and NOW functions are numbers. This means that you can change the format the display the date and time differently if needed.

Automatically Insert Date and Time Using a Script

The drawback of using functions is that it recalculates and changes the date/time value every time there is a change.

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

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

Here are the steps:

  1. Click the Tools button
  2. Click the ‘Script Editor’ option.
    insert-timestamp-in-google-sheets-script-editor

In the script editor code window, copy paste the following code (credit: Stackoverflow):
function onEdit() {

var s = SpreadsheetApp.getActiveSheet();

if( s.getName() == “Sheet1″ ) { //checks that we’re on the correct sheet

var r = s.getActiveCell();

if( r.getColumn() == 1 ) { //checks the column

var nextCell = r.offset(0, 1);

if( nextCell.getValue() === ” ) //is empty?

nextCell.setValue(new Date());

}

}

  1. }
insert-timestamp-in-google-sheets-code
  • Save the code.

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

You can change the column number in the script above based on your document. For example, in our example sheet below, want the date to appear in the third column based on column B.

Example sheet for inserting timestamps

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

Change the column number to the column you want to use as the condition. In our case, it is column B, so I will change the 1 to 2.

Below that we have the offset option var nextCell = r.offset(0, 1);

This gives the cell that we want to insert a timestamp. The first number represents the row, and we have it at 0 since we want the timestamp in the same row as the data. The second number stands for how far we want to offset the date column from the condition column. You can choose the column you want your timestamp in.

In our case, we want it in column C, so we’ll change the var. Offset to var nextCell = r.offset(0, 2);

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

Insert Timestamp Using IF and IFS Function

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

using a checkbox

We can make it so that when the order is fulfilled, and the status checkbox is ticked, then the cell for date and time will automatically be filled with the time the checkbox is ticked.

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

In this case, we will use the formula:

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

If statements and timestamps

For a checkbox, we will use B3=TRUE since they use the operation TRUE or FALSE. This means that if the checkbox cell is ticked, then it is True, so the formula will return the date and time for “Now”

If we did not have checkboxes in our sheet, then we would use “Yes” instead of “True

Frequently Asked Questions

How Do You Create a Timestamp When a Cell is Updated?

You can use the above code in this article for creating automatic time stamps in the Google Apps Script editor. Then, you can change the Triggers on 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

Wrapping Up

Hopefully, this guide answered all the questions you had about how to insert a timestamp in Google Sheets. Although using the script can be a bit difficult for newer users, you can often get away with just using the keyboard shortcuts mentioned in the beginning of the article.

Related:

Popular Posts

8 thoughts on “Easily Insert a Timestamp in Google Sheets”

  1. Thanks for this script …
    But i Have a problem .
    when i get a new date in column B .
    if we add a protected for these cells on column B , other users who we share this file with them , can’t use this script , when they add any value on Column A we didn’t get new date for Column B .
    Only for Editor who can access this file , can get new date when he enter any value on Column A .
    After i canceled a protected , all of our users can proceed .
    -Problem on Protected cells .
    can this script solve with protected cells .

  2. In the script editor code window the section that says, myFunction says onEdit on mine?

    Is this why it isn’t working??

  3. Hi, I need to use the timestamp junction if the precious column is a drop down list. Could you tell me what the script would be then?

  4. I would like to know if there is a way to add the timestamp to a Google Sheet that is already filled in and growing with data that would tie into version history?
    In other words, I’m given access to a sheet to edit collaboratively and want to be able to start sorting with newest additions at the top.
    Is there a way to add in a timestamp that would be able to correctly timestamp everything on the sheet from when it was created going forward in the collaboration?

  5. Is there a way to show the identity of the person who entered the time/date stamp? ( maybe their computer sign on? )

  6. Your timestamp script worked well for me, thank you. Two questions:
    1) Why does the time only show up in the function box at the top, and not the cell? Only the date shows in the cell.
    2) Is there a code I could insert into this script that would allow for 2 columns to timestamp? Example – if cell A1 has text but A2 does not, then A3 would timestamp. And if both A1 and A2 have text, then a timestamp would still occur in column 3.
    Sometimes I only need to fill in A1, and sometimes I need to fill in both A1 and A2 with data.

    Thank you

Comments are closed.