3 Easy Ways to Timestamp Google Sheets

Time-stamping your Google Sheets can allow you to keep a record of when data is created or deleted in your spreadsheet.

Here are three ways you can use timestamps to keep everything organized in your spreadsheets.

Timestamp Method 1

Using the built-in shortcuts

If you want to insert timestamps in some places in a spreadsheet, using the keyboard shortcut is the easiest and the simplest way to do it.

Shortcuts for PC:

• Insert time: Ctrl + Shift + ;
• Insert date: Ctrl + ;
• Insert date and time: Ctrl + Alt + Shift + ;

Shortcuts for Mac:

• Insert time: ⌘ + Shift + ;
• Insert date: ⌘ + ;
• Insert date and time: ⌘ + Option + Shift + ;

Using the built-in formulae

You can use some built-in formulae into Google Sheets to timestamp your spreadsheet, however, it is important to note that these formulae are not static.

Therefore, the timestamp entered through these formulae will change every time you enter information or make a change in your spreadsheet.

• To insert the current date, use the following formula:

=TODAY()

• To insert the current date and time, use the following formula:

=NOW()

Timestamp Method 2

Using an IFS statement to automatically timestamp cells

In order to automatically timestamp in Google Sheets when information is entered, we need to use an IFS formula with a circular reference which requires a setting that is usually disabled as a pre-set.

Follow the following steps to see how you can timestamp the information in your spreadsheets using an IFS statement.

Step 1: Resolving the iterative calculation

• Click on File, and then click on Spreadsheet Settings

• In these settings, click on Calculation.
• Then turn the Iterative Calculation On using the dropdown.
• Set Max number of iterations at 1.
• Click on Save Settings.

Step 2: Formatting the timestamp column

• Select the column you want the timestamps to be entered in

• Click on Format > Number > Date time or you can pick the format of your choice

Step 3: Using the IFS statement to timestamp

• Enter the following formula in the first cell of the timestamp column, where A2 is the first cell of the column you want to insert information in and B2 is the first cell of the timestamp column

=IFS(A2="","",B2="",NOW(),TRUE,B2)

• Hit Enter
• Drag the formula in the first timestamp cell, to the cells underneath it to apply the formula to the entire column
• The timestamp cell will remain blank unless something is entered into the Activity cell next to it.
• Every time you type something in the Activity column, a timestamp will appear in the cell next to it, which will remain static after the first time information is entered into it, and will not change even if you proceed to make changes to it at a later time

Time-stamp Method 3

Timestamp using a script editor

• Click on Tools > Script Editor

• In the window for the script editor, paste the following code

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());

}

}

}

• Click on Save

• Every time you type something in the spreadsheet, a timestamp will automatically appear in the cell next to it.

References

• Keyboard Shortcuts for Google Sheets
• Stackoverflow (for the code)