How to Insert a Static Timestamp in Google Sheets

This guide will explain how to insert a static timestamp in Google Sheets.

Timestamps are an important feature that allows you to record the specific date and time when a certain event occurs, like indicating when a spreadsheet was last updated or when a row in a table was filled out.

While Google Sheets offer various methods to add timestamps, it also has functions that can generate timestamps that automatically update. 

In this guide, we will provide a step-by-step tutorial on how to insert static timestamps in your spreadsheet.

 

A Real Example of Inserting a Static Timestamp in Google Sheets

Converting Dynamic Timestamps to Static Timestamps

Let’s explore a simple example where we must insert a static timestamp.

last updated

In the table above, we want to enter the current date and time into cell B1. Users can type a timestamp themselves; however, any form of manual input is  susceptible to user error.

fill out timestamp using NOW function

In the example above, we were able to enter the current date and time using the formula =NOW(). However, the NOW function will always recalculate its value and will not hold the same value from when it was first entered. 

copy value from NOW function

A workaround for this is using the Paste values only action (Ctrl + Shift + V) to copy and paste the value of the NOW function. After copying the timestamp and pasting it elsewhere, the user can now delete the NOW formula and be left with a single static timestamp.

Using Keyboard Shortcuts to Insert Static Timestamps

While copying the current timestamp from the NOW function is a workable solution, there is an even easier way to insert static timestamps in Google Sheets.

keyboard shortcuts for static timestamps

In the image above, a table shows three useful keyboard shortcuts to insert static timestamps in any cell. For example, typing Ctrl + Alt + Shift + ; while a cell is selected will insert both the current date and time into the selected cell.

Using the LAMBDA function to Insert Static Timestamps

Another use case for static timestamps is to keep track of when a particular cell was modified. 

sample table that requires timestamps

In the table above, we have a field where users can enter their email addresses. We want the TIMESTAMP field to automatically generate a timestamp value when a user inputs text into the Email Address field.

add email address to automatically add timestamp

In the image above, a timestamp was generated in column A after a user placed their email address in the next column. 

We can use the following formula to achieve this:

=LAMBDA(timestamp,IF(B4<>"",timestamp,""))(NOW())

The LAMBDA function will allow us to use the NOW function once and store it as a static timestamp. Let’s take a closer look to see how the formula works.

Our LAMBDA function accepts a single named argument that we’ve labeled timestamp. The NOW function outside of LAMBDA will be stored in the timestamp value and substituted later when evaluating the formula defined in LAMBDA’s second argument.

The LAMBDA function above defines an IF function that checks if the target cell is not empty. If the cell is empty, we’ll return an empty string (“”). Otherwise, the IF function returns the value stored in the timestamp argument.

When a user enters a value in the target cell, the LAMBDA function will run the NOW function and copy its value into the IF function. Since the cell is no longer empty, the function will return the stored timestamp value.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to insert a static timestamp in Google Sheets.

 

How to Insert a Static Timestamp in Google Sheets

  1. Select the cell you want to enter a static timestamp on.
    select cell to insert static timestamp in google sheetsIn this example, we’ll add a static timestamp on cell B1 to indicate the last time the user updated the current spreadsheet.
  2. Use the keyboard shortcut Ctrl + ; (Windows) or Cmd + ; (Mac) to insert the current date.
    use keyboard shortcut to insert a static timestamp in Google Sheets
  3. Use the keyboard shortcut Ctrl + Shift + ; (Windows) or Cmd + Shift + ; (Mac) to insert the current time instead.
    insert a static timestamp in Google Sheets
  4. Use the keyboard shortcut Ctrl + Alt + Shift + ; (Windows) or Cmd + Option + Shift + ; (Mac) to insert the current date and time as a timestamp.
    insert a static timestamp in Google Sheets
  5. We can use a Google Sheets formula to insert a static timestamp once the user adds data to a target cell. Start by selecting the cell where you want to place the timestamp.
    select cell to place lambda function
  6. Enter the formula =LAMBDA(timestamp,IF(B4<>””,timestamp,””))(NOW()) into the target cell. Replace B4 with the cell you want to use as a trigger for the static timestamp.
    insert a static timestamp in Google Sheets
  7. Use the Auto-Fill feature to copy the LAMBDA formula to the remaining cells in the timestamp column.
    insert a static timestamp in Google Sheets
  8. Adding input to the target cells will trigger the LAMBDA formula to output a timestamp.
    automatic static timestamp upon data entry

After following these steps, you should now be able to insert static timestamps on your spreadsheet.

 

If you found this post helpful, you may also be interested in reading our post on how to extract dates from a timestamp in Google Sheets.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks! 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like