3 Easy Ways to Automatically Insert Timestamps in Google Sheets

Sometimes, you may want to quickly insert a timestamp in Google Sheets to mark when the activity was done.

There are multiple ways of adding a timestamp in Google Sheets and in this tutorial, I will show you three such methods.

Each method has it’s pros and cons and I have listed these clearly.

Keyboard Shortcut to Insert TimeStamp

If you want to quickly insert a timestamp in one cell (or a few cells), you can use the below keyboard shortcuts:

To Insert the current date

Control + : (hold the Control key and press the colon key)

To insert the current time

Control + Shift + : (hold the Control and Shift keys and press the colon key).

Note that these keyboard shortcuts would insert a static date and time value. This means that if you make any changes in the worksheet or close and open it, these date/time values will not change.

In case you want to get a combined value of date as well as time, you can first use these two keyboard shortcuts to insert the date and time and then simply add these two cells (as shown below).

Remember to convert the formula to value to make sure the combined timestamp (with date and time) is a static value (hint: use Paste Special to do this).

Formula to Insert TimeStamp

There are a couple of formulas that you can use to quickly insert the date and time in Google Sheets.

To insert the current date, use the below formula:

=TODAY()

To insert the current date as well as the current time, use the below formula:

=NOW()

Note that these formulas are volatile, which means that if you change any cell in the Google Sheets document, it will lead to a recalculation and make these formulas to update.

So if you use the TODAY function in a cell and open that Google Sheet document the next day, the formula will update to show you the current date.

In case you want these to be static values, you can convert the formula to value (or use the keyboard shortcuts covered above).

Also, the result of these formulas is a number (as all date and time values are stored as numbers in Google Sheets). This allows you to format the result and show it in different formats.

For example, if you have a date as 01-01-2020, below are some of the formats you can use to display it:

• 01 January 2020
• January 01, 2020
• 01/01/2020
• 01 Jan 2020

Similarly, with time, you can display it in the AM/PM format or in 12/24 hour format.

Automatically Insert Time Stamp in Google Sheets Using a Script

While both the above methods (keyboard shortcuts and formulas) work well, these would not automatically insert the timestamp for you.

For example, suppose you have a dataset where you’re tracking activities and you want a timestamp to be inserted as soon as an activity is added to a cell.

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

Below are the steps to use a script to automatically add timestamps in Google Sheets:

1. Open the Google Sheets document
2. Click on the Tools options in the menu
3. Click the ‘Script Editor’ option. This will open the Script Editor in Google Sheets.
4. In the script editor code window, copy-paste the following code (the credit for this script goes to Stackoverflow):

   function onEdit() {
   var s = SpreadsheetApp.getActiveSheet();
   if( s.getName() == "Sheet1" ) { //checks that we're on Sheet1 or not
   var r = s.getActiveCell();
   if( r.getColumn() == 1 ) { //checks that the cell being edited is in column A
   var nextCell = r.offset(0, 1);
   if( nextCell.getValue() === '' ) //checks if the adjacent cell is empty or not?
   nextCell.setValue(new Date());
   }
   }
   }

   

5. Save the code by clicking on the Save icon in the toolbar. It may ask you to give it a name.

Once you have this code in place, whenever you enter anything in any cell in Column A, a timestamp would automatically appear in the adjacent cell in Column B. Note that this timestamp is static, which means it won’t change when you make any change in the worksheet.

The above code only works on Sheet1 (as we have specified that in the third line. You can change the name “Sheet1” to whatever sheet you want this code to work on.

Also, the code has been created considering you are entering the data in column A and need the time stamp in Column B. You can modify these as required.

I hope you found this tutorial about inserting the timestamp useful!

You may also like the following Google Sheets tutorials:

• How to Insert BULLET POINTS in Google Sheets
• How to Insert CheckBox (Tick Box) in Google Sheets
• How to Get the Word Count in Google Sheets