A Simple Timestamp Google Sheets Tutorial for 2024

Occasionally, you may need to keep track of work by using a timestamp. This simple record is a great way to illustrate precisely when activities were carried out. It’s also a regular part of my everyday workflow. That’s why I made this timestamp Google Sheets tutorial.

Below, I’ll show you the quick and easy hacks to show time and date info in your file. As always, I include practical examples from my life.

Using Keyboard Shortcuts to Insert Timetamps in Google Sheets

To quickly insert a timestamp in one cell (or a few cells), you can use the below keyboard shortcuts. Note that these keyboard shortcuts insert a static date and time value. If you make any changes in the worksheet – then close and reopen it – these date/time values won’t change. I’ll show you how to insert timestamps for Windows and Mac below. Basically, that means swapping out the “control” button for the “command” button and the “alt” button for the “option” button. Easy, right?

Timestamp Shortcuts for Windows :

  1. Insert the current date: Control + : 
  2. Insert the current time: Control + Shift + :
  3. Insert the current date and time: Control + Alt + Shift + ;
Shortcuts to Insert date and timestamp in Google Sheets

Timestamp Shortcuts for Mac

  1. Insert the current date: Command + : 
  2. Insert the current time: Command + Shift + :
  3. Insert the current date and time: Command + Option + Shift + ;

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

Combine Date and Timestamp by adding it

Formulas to Insert Timestamps in Google Sheets

If you want to insert a timestamp that updates, you’ll want to try a different method. To do this, I’m including a few different options. Note that you can use several formulas to insert timestamps in Google Sheets.

Let’s start with volatile formulas. With these, if the Sheets cell is changed, it will recalculate and update what appears in the cell. For example, if you used the TODAY function in a cell and opened that Google Sheet document the following day, the formula would update to the current date.

If you want these to be static values, you can convert the formula (or use the keyboard shortcuts covered in the previous section).

Inserting Current Date

To insert the current date, use the below formula:

=TODAY()

Today function in Google Sheets

Here’s how to use the formula:

  1. Click on the cell you want to add a timestamp to.
  2. Type =Today or select it from the formula suggestions.
  3. Click Enter.

These formulas will be shown as a number. Users can format the results in several different formats. For example, if the date is 01-01-2032, it can be displayed as:

  • 01 January 2032
  • January 01, 2032
  • 01/01/2032
  • 01 Jan 2032

Not sure how to do it? I discussed how to change date formats. It’s a pretty simple process. I even made a video about it on YouTube.

Inserting Current Date & Time

Sometimes you’ll want to insert the current time along with the date. That requires a different function. To insert the current date as well as the current time, use the formula below:

=NOW()
NOW function in Google Sheets

Here’s how to use the NOW formula:

  1. Click on the cell you want to add a timestamp to.
  2. Type = Now or select it from the formula suggestions.
  3. Click Enter.

Note: You can display the time in AM/PM format or military hour format. Military hours help you avoid having to distinguish morning from afternoon and evening hours, so they’re pretty standard in my workflow.

Timestamp Google Sheets Script

The main reason I created this guide was to show how I built a script that automatically inserts a timestamp in Google Sheets. I use this along with a little more code to automatically insert a time stamp when someone clicks a check box on my sheet. That dramatically changes my sheet. With this ability, I’m able to share it with my coworkers so I can follow along with their progress as they complete a series of tasks.

 

 

 

I think it’s a major time saver. It might not seem like much to update an individual time, but that stacks up when my teammates complete a bunch of tasks in a single day. My little code block saves boatloads of time, and it’s the best way to track tasks in Google Sheets.

So why use a timestamp Google Sheets script? While both keyboard shortcuts and formulas work well, these won’t automatically insert anything unless you program them to do so. That’s what I did.

Here’s how you can apply my method: Suppose you have a dataset where you’re tracking activities. You want a timestamp to be inserted as soon as an activity is added to a cell. This can be done with a simple Google Sheets script.

Note: The following code only works on Sheet1. It has also been created for entering the data in column A and receiving a timestamp in Column B. If I want to update this to reference another sheet, I have to change that name to whatever the sheet is called.

Here’s how to use a script that automatically adds timestamps in Google Sheets:

  1. Open the Google Sheets document.
  2. Click on Tools > Script Editor.
Click on the Script Editor Option

In the script editor code window, copy-paste the following code (credit for the original 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 for the column we're using is column A
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) //checks whether the adjacent cell is empty or not
        nextCell.setValue(new Date());
    }
  }
}
Insert TimeStamp Code in the Script Editor in Google Sheets

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

Now, when anything is added in any cell under Column A, a timestamp automatically appears in the adjacent cell in Column B. While this timestamp is static, it won’t change when you make any change in the worksheet.

Related reading: How to Add and Subtract Time in Google Sheets

How do you automatically insert timestamps in Google Sheets?

You can use my timestamp script to automatically insert a timestamp into Google Sheets. This automates the process. That means you don’t need to fill out a formula every time you need to mark a date. I use this on several of my most commonly-used spreadsheets for work. It’s especially valuable when tracking tasks for my team.

How do you choose a different column in the Google Sheets timestamp script?

The key to this question in, of course, in the code. See the note in the code block that shows where you’re checking whether you’re in column A? That block is set to if( r.getColumn() == 1 ) {  as a default. Just change the 1 to a 2 to get column B. Or change it to 3 to get column C. Here’s how it would look if you wanted to check column D instead: if( r.getColumn() == 4 ) {

How do you add custom code in Google Sheets?

I regularly enhance my Google Sheets interface with custom code. To do this, just choose “Script Editor” from the Tools menu. I have a whole guide on the Script Editor in Google Sheets.

Google Sheets Hacks: Beyond Timestamps

I hope that my timestamp Google Sheets tutorial has been useful! If you’re looking to expand your knowledge of spreadsheets, I’ve got you covered. My site has a bunch of spreadsheet hacks. My goal is to help you save time, so you can work as efficiently as possible. Here are a few other articles you may want to check out:

Thanks to all of my awesome commenters in the community below. They have some smart advice for additional changes you may want to make in your Google Sheets timestamp script.

Most Popular Posts

75 thoughts on “A Simple Timestamp Google Sheets Tutorial for 2024”

  1. Well – i can get the date stamp to show up – but it does NOT update when i amend the text in the adjacent cell. How do we amend that?
    Secondly, if i want the data format in non-US format, how do I fix that (to e.g. YYYY-MM-DD)?

    Reply
    • Change your location settings in Spreadsheet settings. Your sheet wil;l show UK or wherever then. Also, within the Format,/Number/More Formats you can specify to display DD/MM/YY HH:MM:SS I use this format.

      Reply
    • The script checks the date field, and only sets it if the field is empty.

      Just remove the test (and be sure you remove the closing } for the last if too.)

      You set the display format in by marking the row and setting the format, as date and time is stored as a decimal number, not a formatted string.

      Reply
    • …to answer the secondly part, this worked for me:

      nextCell.setValue(new Date()).setNumberFormat(“YYYY-MM-DD”);

      The cell will show the correct format, but if you click on the cell it will still show the full Date()

      Reply
    • In the last line of the code, replace the (new date) with this:

      (new Date()).setNumberFormat(“yyyy-MM-dd hh:mm”)

      This will insert date and time. Remove the hh:mm portion if you do not want time displayed.

      Reply
      • I need to choose second row(B) can you pls give me code because in 1st row i have index number if i write in b then autometic count num ty

        Reply
    • Yes. Instead of
      “if( s.getName() == “Sheet1″ )”, use “if(s.getName() == (“Sheet1” || “Sheet2” || “Sheet3″))”.

      If you do it like this, the columns will have to match tho, so another option is to just repeat the code but change the conditional

      “if name = blabla1 do timestamp 1
      else if name = blabla2 do timestamp 2
      else if name = blabla3 do timestamp 3”

      etc

      Reply
    • Yes, you just need to add the other sheets names so for example:

      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

      if(r.getDisplayValue() == “Closed”)
      var nextCell = r.offset(0, 1);

      if( nextCell.getValue() === ” ) //checks if the adjacent cell is empty or not?
      nextCell.setValue(new Date());
      }
      }
      if( s.getName() == “Sheet2” )
      { //checks that we’re on Sheet2 or not
      var t = s.getActiveCell();

      if( t.getColumn() == 1 )
      { //checks that the cell being edited is in column A

      if(t.getDisplayValue() == “Closed”)
      var nextCell = t.offset(0, 1);

      if( nextCell.getValue() === ” ) //checks if the adjacent cell is empty or not?
      nextCell.setValue(new Date());
      }
      }
      if( s.getName() == “Sheet3” )
      { //checks that we’re on Sheet3 or not
      var v = s.getActiveCell();

      if( v.getColumn() == 1 )
      { //checks that the cell being edited is in column A

      if(v.getDisplayValue() == “Closed”)
      var nextCell = v.offset(0, 1);

      if( nextCell.getValue() === ” ) //checks if the adjacent cell is empty or not?
      nextCell.setValue(new Date());
      }
      }
      }

      Hope this helps 🙂

      Reply
    • I just deleted this line:
      if( s.getName() == “Sheet1” ) { //checks that we’re on Sheet1 or not
      and is seems to work for the whole sheet.

      Reply
    • Yeah, you can define that on line 5. Instead of
      “if( r.getColumn() == 1 ) {” you’re gonna have to write “if( r.getColumn() == 11 && r.getRow() >= 5 && r.getRow() <= 21) {"

      (This particular example checks if the cell is on column K and between lines 5 and 21, both included)

      Reply
  2. This simple method worked for me. Create script,

    function timestamp() {
    return new Date();
    };

    In the cell where I want a timestamp if another cell changes I add,

    =if(isblank(K32),””,timestamp())

    Reply
  3. Hi all,
    Can someone help me with this problem. I have timestamp script running in google sheet and it work fine when I manually change value in cell. But I can’t find any solution how to have timestamp working when entry in trigger cell is some auto data from formula or such. Or maybe if someone have another solution. What I’m doing now is, from sheet1 with Query transfer data to sheet2 when some change is made. When new row is created in sheet2 I want to have date of entry for every row that is created in sheet2, and date need to be fix all the time.

    Reply
    • Good question here. How about something like this? I have the script below functioning so whenever a new row is added to Sheet2, the timestamp is automatically inserted into column A of that row.


      function onEdit(e) {
      var range = e.range;
      var sheet = range.getSheet();

      // Check if the edited range is in Sheet2 and the timestamp column
      if (sheet.getName() == "Sheet2" && range.getColumn() == 2) { // Assuming the timestamp column is column B
      var row = range.getRow();
      var timestampCell = sheet.getRange(row, 1); // Assuming the timestamp should be in column A
      if (!timestampCell.getValue()) { // Check if timestamp is not already set
      timestampCell.setValue(new Date());
      }
      }
      }

      Reply
  4. Is it possible to do the scripting one on two separate columns? I swear I had it working but now it seems to only execute one script no matter what I do.

    Reply
    • Sure! Use an “onformsubmit” instead of “onedit”.


      function onFormSubmit(e) {
      var sheet = e.range.getSheet(); // Get the sheet where the form data is submitted
      var user = Session.getActiveUser().getEmail(); // Get the email of the user who submitted the form
      var timestamp = new Date(); // Get the current timestamp
      var row = e.range.getRow(); // Get the row where the form data is submitted

      // Assuming the timestamp column is column B, adjust the range accordingly
      var timestampCell = sheet.getRange(row, 2); // Get the cell in the same row, but column B for timestamp
      var userCell = sheet.getRange(row, 3); // Get the cell in the same row, but column C for user email

      // Set the timestamp and user email in the corresponding cells
      timestampCell.setValue(timestamp); // Set the timestamp in column B
      userCell.setValue(user); // Set the user email in column C
      }

      Reply
    • Just add in an “onedit” into the scrip. Here’s how it might look:

      function onEdit(e) {
      var range = e.range; // Get the edited range
      var sheet = range.getSheet(); // Get the edited sheet
      var user = Session.getActiveUser().getEmail(); // Get the email of the user who made the change
      var timestamp = new Date(); // Get the current timestamp

      // Check if the edited range is in a specific sheet and range
      if (sheet.getName() == "Sheet1" && range.getColumn() == 1 && range.getRow() >= 2) {
      var timestampCell = sheet.getRange(range.getRow(), 2); // Get the cell in the same row, but column B for timestamp
      var userCell = sheet.getRange(range.getRow(), 3); // Get the cell in the same row, but column C for user email
      timestampCell.setValue(timestamp); // Set the timestamp in column B
      userCell.setValue(user); // Set the user email in column C
      }
      }

      Reply
  5. Thank you for this very helpful and useful post! I have the same question as Rory. I used the code you supplied and it inserts a timestamp for the first line of data entered only. How do I make it enter that date for all rows with new data entered? Thanks!

    Reply
  6. i used this code the sources range is contented with if formula which i am using, auto timestamp not working it is working only when manual change in cell.

    Reply
  7. to get the date in column A when date is in column B I changed from this:
    if( r.getColumn() == 1 ) { //checks that the cell being edited is in column A
    var nextCell = r.offset(0, 1);

    to this:
    if( r.getColumn() == 2) { //checks that the cell being edited is in column B
    var nextCell = r.offset(0, -1);

    Reply
  8. Time stamp script when offline.

    Is it possible to use the script or a modified version of it when offline and not connected to the internet?

    We’re trying to use this for our video shoots and we’re always at lakes which often have horrible to no cell service.

    Reply
    • No problem! That’s a rare use case, but there’s a workaround. To add time stamps offline, use the onEdit trigger to automatically add a timestamp when a cell is modified. This trigger will work even when offline, but the changes will be applied once the spreadsheet regains an internet connection.

      Here’s a simplified example of a script:
      function onEdit(e) {
      var sheet = e.source.getSheetByName('YourSheetName');
      var range = e.range;
      var timestampColumn = 2; // Adjust the column index where you want the timestamp

      if (range.getColumn() !== timestampColumn) {
      sheet.getRange(range.getRow(), timestampColumn).setValue(new Date());
      }
      }

      Reply
  9. Is there a way to remove the portion that checks to see if the cell is empty? I would like it to overwrite the new timestamp any time the previous column is changed.

    Reply
  10. If I add the script to my Google sheet and I share the sheet with others (as Editors), how can the script be used for when they enter a particular field?

    Reply
  11. it’s generating both the date and time, and because of that it’s not consolidating in my pivot table. Anyone know how to fix that?

    Reply
  12. Works a treat. Is there a way to get it to work with a webhook. I have GHL pushing across new rows and the don’t seem to get updated

    Reply
  13. What would I change so the script would insert the date in column J on my spreadsheet?

    I also change the setDate line to (new Date()).setNumberFormat(“yyyy-MM-dd hh:mm”) as suggested above an it appears to stamp both date and time but I can only see the time if I click on the cell and look in the data line above the spreadsheet. In the actual cell is only shows the date. How do I change this to display the time also in the cell.

    Thank you in advance for the help

    Reply
    • Click at the top of of the column to highlight the entire column, check 123 with a drop down arrow (Meaning more format) and select Date time. That should resolve the issue

      Reply
  14. Please Help. Once you put this in, should it work automatically or do you have to do something else because it is not working for me.

    Reply
  15. I would like to do it for multiple collumn, for example if i write in A1 then E1 is create the stamp,
    then if i write on B1, F1 is create another stamp which is differ from A1 and E1.
    is it possible?

    Reply
  16. Hi!

    I’d like to use the above script, except in column A I have a drop down that is “yes” or “no” and I would like the date stamp to show up in column B when I click the “yes” – so basically modify this script so that it works for a specific entry in column A, not necessarily any entry.

    Reply
  17. Can I use it to all my sheets? Because I can see the date only in one, but I need the date to update in 3 different sheets

    Reply
  18. The “Script Editor” is no longer an option in Google Sheets. I found an Apps Script under Extensions, and put the code there, but can’t seem to find a way to get it to run on my Sheet. :-\

    Reply
  19. This was really useful. I had to edit it a bit though. I didn’t want any stamp left for blank data. I also didn’t want it to be one time only. So I came up with the following slightly altered version:

    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( r.getValue() === ” ) { //checks if the cell being edited is empty or not?
    nextCell.setValue(”);
    }
    else
    {
    nextCell.setValue(new Date());
    }
    }
    }
    }

    Note that there’s one flaw with this: If you change a bunch of cells all at once, e.g. by using delete or paste, only the top one will trigger. Does anyone know how to resolve this, please?

    Reply
  20. This works great, just wanna ask how to remove timestamp when there is no data in the specified cell or there was data in the cell and then edited for the next day.

    Reply
    • You can use the NOW() function and then format the cell to only show the time in Format > Number > Custome time and date

      Reply
  21. If I were to add the date in the 7th column, do I change this as under:

    var nextCell = r.offset(0, 7);

    Also do I need to add a trigger, if so, what is the arrangement?

    Reply
  22. How do I change the columns? Instead of inputting items in Column A, I want them in Column B. And vice versa for the timestamp. I want them in Column A instead of in B.

    Reply
  23. Hi,
    Thank you for this information. It has worked a treat. I did have one question though for my own usage. If I wanted this to remove the date if the checkbox is unchecked, is there a way to do that? Thanks.

    Reply
  24. how to get list of items sold each day in excel, example sheet is:
    A1 (item name ) , B1 ( item qty), C1 (sold quantity)
    if C1 is entered 2 items sold , how to get item name in another row each day. like a list of items sold daily

    Reply
  25. Hi,
    When you enter data in a column in google e-tables with multiple users,
    How to stamp the date in the column next to the username.

    Reply
    • You can do this with an App Script:

      function onEdit(e) {
      var sheet = e.source.getSheetByName('YourSheetName'); // Replace 'YourSheetName' with your actual sheet name
      var range = e.range;

      // Check if the edited range is in the desired column (e.g., column B)
      if (range.getColumn() == 2) {
      // Get the username from the edited row in column A
      var username = sheet.getRange(range.getRow(), 1).getValue();

      // Stamp the current date in the column next to the username
      sheet.getRange(range.getRow(), 3).setValue(new Date());
      }
      }

      Reply
  26. I would just like to add a timestamp to the title, so when it’s printed, it prints the date automatically.
    I do not want to put in a separate field because that makes sorting difficult.

    Reply
    • You can dynamically generate a timestamp and include it in the title of your PDF document.

      Try this:
      function convertSheetToPDFAndEmail() {
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = spreadsheet.getSheetByName('YourSheetName'); // Replace 'YourSheetName' with the actual sheet name
      var range = sheet.getDataRange();
      var data = range.getValues();
      var urlColumnIndex = 1; // Adjust this to the column index where image URLs are located

      // Create a timestamp for the title
      var timestamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMdd_HHmmss');

      // Fetch all image URLs
      var imageUrls = [];
      for (var i = 1; i < data.length; i++) { var imageUrl = data[i][urlColumnIndex - 1]; // Adjust to 0-based index imageUrls.push(imageUrl); } // Create a temporary folder var folder = DriveApp.createFolder('TempFolder'); // Fetch and resize images var images = []; for (var i = 0; i < imageUrls.length; i++) { var imageUrl = imageUrls[i]; var imageBlob = UrlFetchApp.fetch(imageUrl).getBlob(); var resizedBlob = resizeImage(imageBlob, 200, 200); // Adjust the width and height as needed var imageFile = folder.createFile(resizedBlob); images.push(imageFile.getBlob()); } // Create a temporary sheet for PDF conversion var tempSheet = spreadsheet.insertSheet('TempSheet_' + timestamp); // Include timestamp in the sheet name tempSheet.getRange(1, 1, data.length, data[0].length).setValues(data); // Insert resized images into the temporary sheet for (var i = 0; i < images.length; i++) { tempSheet.insertImage(images[i], 1, data.length + i + 1); // Adjust column index } // Convert the temporary sheet to PDF var pdf = folder.createFile(tempSheet.getAs('application/pdf').getBytes()); // Email the PDF to the user MailApp.sendEmail({ to: 'user@example.com', // Replace with the recipient's email address subject: 'PDF_with_Images_' + timestamp, // Include timestamp in the subject body: 'Please find the attached PDF with images generated on ' + timestamp + '.', attachments: [pdf] }); // Clean up: Delete the temporary folder and sheet folder.setTrashed(true); spreadsheet.deleteSheet(tempSheet); } function resizeImage(blob, width, height) { var base64 = Utilities.base64Encode(blob.getBytes()); var resizedBase64 = Utilities.base64EncodeWebSafe(Utilities.newBlob(Utilities.base64DecodeWebSafe(base64)).getResize(width, height).getBytes()); return Utilities.newBlob(Utilities.base64DecodeWebSafe(resizedBase64)); }

      Reply
  27. I am creating a laboratory log and I want to reverse the function so that every time I add data to any column other than A, Column A populated the date on that row? Can someone help me, I have no clue what I am doing?

    Reply
  28. is there any way that next cell will only be updated if the Column is edited by a specific string?

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

    }

    }

    }

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!