3 Easy Ways to Timestamp Google Sheets

Here are 3 ways to use timestamps in Google Sheets to record when data is created or deleted and keep your spreadsheets organized.

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

upf

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

upf

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
Convert Currency in Google Sheets
  • 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.
Convert Currency in Google Sheets

Step 2: Formatting the timestamp column

  • Select the column you want the timestamps to be entered in
Convert Currency in Google Sheets
  • Click on Format > Number > Date time or you can pick the format of your choice
Convert Currency in Google Sheets

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)

Convert Currency in Google Sheets
  • 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
Convert Currency in Google Sheets

Time-stamp Method 3

Timestamp using a script editor

  • Click on Tools > Script Editor
Convert Currency in Google Sheets
  • 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());

}

}

}

Convert Currency in Google Sheets
  • Click on Save
Convert Currency in Google Sheets
  • Every time you type something in the spreadsheet, a timestamp will automatically appear in the cell next to it.

References

Google Workspace Marketplace

Start Your Free Trial

Try Tiller free and track all your accounts in one place, always know where your money goes, and confidently plan your financial future.

Default image
Tiller Money
Your financial life in a spreadsheet, automatically updated each day.