Timestamps are used to log the date and time when information has been accessed or updated.
They are commonly found in databases, especially to save the last time a user logged in to the system, or when a record was last updated.
There are times however, when they are also useful in spreadsheets.
We may want to know when entries in a sheet containing contact details were last updated.
This is a perfect case for a timestamp.
We’ll be looking at three ways of adding a timestamp to a Google sheet, starting with the simplest using keyboard shortcuts.
Next we take a look at using a formula, and finally writing a custom script.
For the custom script method, we will be using an example Google sheets contact list.
While the term timestamp more commonly refers to the date and time, for our purposes, we will use it interchangeably to refer to any of time, date, or date and time.
Time and Date Formatting in Google Sheets
Before we begin, it should be noted that the format of the date and time depends on the locale and time zone settings for the spreadsheet.
These settings are available from the main Google sheets menu under File > Settings.
This will open the Settings for this spreadsheet dialog box.
Here, you can check and change the Locale, Time zone, and Display language for your sheet.
If for some reason you want to use a format that is different from that of the current spreadsheet, without changing the default settings, you can always apply a custom format to the cell, using the Format > Number > Custom date and time menu option.
This will open the Custom date and time formats dialog box from which you can choose one of the preset formats or create your own.
Insert Time, Date, Timestamp Keyboard Shortcuts
To quickly add the current time, date, or timestamp in any cell, use the keyboard shortcuts given below.
- Ctrl – ; (Control Semicolon) will insert the current date.
- Ctrl – Shift – ; (Control Shift Semicolon) will insert the current time.
- Ctrl – Alt -Shift – ; (Control Alt Shift Semicolon) will insert the current date and time, commonly referred to as the timestamp.
One thing to note with this approach is that the time, date, and timestamp are fixed.
They do not update if and when any changes to the sheet are made.
Of course, you can always update the date/time manually, but that really is not a practical solution.
Insert Time, Date, Timestamp with a Formula
You can add the time, date, or timestamp to any cell with the use of the functions NOW() and TODAY(). TODAY() prints the current date, and NOW() the current timestamp.
There is no function to only get the current time, but we can extract the time, with the aid of the TEXT() function, from the result returned after calling the NOW() function.
The complete formula to do this is =TEXT( NOW() ; “HH:MM:SS” ) where “HH:MM:SS” is the format used to display the time in hours, minutes, and seconds.
If you wanted, you could also just use “HH:MM” to show only the hour and minutes.
It should be noted that the default behavior of the NOW() and TODAY() functions is to automatically update to the current date and time whenever the spreadsheet is recalculated (e.g. when you add a new cell or update the contents of an existing cell), rather than keeping the date and time from when the formula was created.
The behavior can be modified from the menu option File > Settings, and under the Calculation tab in the Settings for this spreadsheet dialog box.
The Recalculation drop down offers a choice from On change, On change and every minute, and On change and every hour.
Script to Insert Timestamp
While using a formula will work perfectly fine in many situations as long as we require a single timestamp to monitor whether a change took place anywhere in the sheet, it would not work if we wanted to timestamp each row in the sheet separately.
To do this, we will need to write a custom script.
Google sheets has its own, powerful scripting language based on Javascript, which ensures it will work on any web browser.
We will use an example contacts sheet where the details for each contact occupy one row.
We therefore require a timestamp on each row so that we can keep track of when each contact was last updated.
To begin, from the Google sheets menu go to Extensions > Apps Script.
This will open the scripting editor in a new browser tab. At the top is the title, Untitled project.
You can rename it to something that makes sense for your sheet. For our purposes, we can leave everything else as is.
We will be entering our formula in the main section below, which by default contains the function declaration myFunction().
Replace all this with the following:
/** @OnlyCurrentDoc */
function onEdit(e){
const sh = e.source.getActiveSheet();
sh.getRange (‘M’ + e.range.rowStart)
.setValue (new Date())
.setNumberFormat (‘MM/dd/yyyy HH:MM’);
}
The line sh.getRange (‘M’ + e.range.rowStart) determines in which column the timestamp will be printed, which in this case is column M.
The line .setNumberFormat (‘MM/dd/yyyy HH:MM’); determines the format in which the timestamp will be displayed.
The resulting script editor page should look like the above snapshot. Click on the Save project icon 
to save the script.
If we now return to our sheet and make a change to any cell in any row, the timestamp column (column M) will be be updated to show for that row, the current date and time.
Conclusion
We’ve looked at three different ways of adding a timestamp to a Google sheet.
The first method allows a user to directly insert the current, time, date, or date and time, using keyboard shortcuts.
This is a very quick way to manually add a timestamp.
This implies that any changes made to the sheet will not update the timestamp.
The second method we looked at uses a formula to add a timestamp as either a date or date and time.
Unlike the keyboard shortcut method, the timestamp will update automatically every time a change occurs to the sheet.
This method uses a single timestamp for the entire sheet.
Finally, we wrote a script that maintains a timestamp for each individual row.
Whenever a change occurs in any cell for any row, the row’s timestamp is automatically updated.
