Download Article Download Article

🤣🙃🤕 If you need to enter timestamps in Google Sheets, doing it manually can be a pain. Luckily, there are several shortcuts you can use to make the process easier. In this article, we’ll show you how to apply a simple script to your spreadsheet to add timestamps automatically, as well as formulas and keyboard shortcuts you can use to timestamp individual cells.

Method 1
Method 1 of 4:
Script Editor

  1. 1
    Click on Extensions. Using a script is an easy way to automatically insert timestamps into a Google Sheets spreadsheet. Once you apply the script, the rest of the process is fully automated. To get started, open your spreadsheet and click on the Extensions tab at the top of the window. [1]
  2. 2
    Select Apps Script. In the dropdown Extensions menu, select the Apps Script option. This will open a new tab where you can create a script that will be bound to your spreadsheet.
  3. 3
    Give your script a name. At the top of the script editor window, you’ll see a box containing the default title Untitled project. Click the box and type in your own title to rename the script.[2]
    • It’s up to you what you want to call your timestamp script. Just choose something that makes sense for your project. For example, you could call it something like "AutoTimeandDate."
  4. 4
    Paste the code into the scripting window. In the Apps Script window, you will see a grey box containing a few lines of code, starting with function myFunction(). Delete whatever is in the box and replace it with a script that will insert a timestamp, such as this one:[3]
    /** @OnlyCurrentDoc */functiononEdit(e){constsh=e.source.getActiveSheet();sh.getRange('M'+e.range.rowStart).setValue(newDate()).setNumberFormat('MM/dd/yyyy HH:MMam/pm');}
    • This particular script will insert a timestamp into the specified column any time you enter data into a cell, in the same row as the data you entered. For instance, if you type something into cell A2, a timestamp will appear in cell M2.
    • There are many different example scripts online that you can use for this purpose. Whichever one you choose, make sure to remove any smart quotes in the code and replace them with straight quotes, or the script won’t work properly.
  5. 5
    Make any changes you like to the code. If you want to edit where and how the timestamp will appear in your spreadsheet, you can make adjustments to the script. For instance, in our example script, you can change which column the timestamp appears in as well as the format of the time and date.
    • The M in line 4 of the script indicates the column where the timestamp will appear. If you want it to appear in a different column, change it to whichever letter or column name you like (e.g., change M to B if you want the timestamp in column B).
    • You can also change the number format in line 7 to display just the time, if you like. Simply delete the MM/dd/yyy
  6. 6
    Click Save. Once you’ve got the script the way you like it, hit the Save project button at the top of the Apps Script project window. This will apply the script to your Google spreadsheet.[4]
  7. 7
    Enter data anywhere in your spreadsheet. Click back over to your Google Sheets tab and enter some data. The timestamp should appear in the same row, in whichever column you specified in your script.[5]
  8. Advertisement

Method 2
Method 2 of 4:
Time and Date Formulas

  1. 1
    Insert the formula =NOW() into a cell to display the time and date. Select the cell where you want to show the date and time, and type in =NOW(). This will cause the current date and time to appear in that cell.[6]
  2. 2
    Use the formula =TEXT( NOW(), "hh:mm:ss" ) to show the current time. If you only want to show the timestamp and not the date, you can modify the formula using the TEXT() function. Copy and paste or type =TEXT( NOW(), "hh:mm:ss" ) into the cell where you want to display the time.[7]
    • If you prefer to display the time using a 24-hour clock instead of a 12-hour clock, capitalize HH in the formula.[8]
    • You can also add milliseconds by adding .000 after the ss at the end of the formula. That is, write the formula as =TEXT( NOW(), "hh:mm:ss.000" ).
    • Add AM/PM to the end of the formula if you’re using a 12-hour clock and want to indicate the time of day more accurately. Format the formula like this: =TEXT( NOW(), "hh:mm:ss AM/PM" ).
  3. 3
    Change how often the timestamp updates in Settings. When you insert a timestamp using a formula, the time is not fixed. By default, it will update any time you make a change to the spreadsheet. While you can’t create a fixed timestamp using this method, you can modify how often it updates.[9] To do this:[10]
    • Open the File menu.
    • Select Settings to open the Settings for this spreadsheet dialog box.
    • Click on the Calculation tab.
    • Under the Recalculation heading, click on the dropdown menu and select how often you want the timestamp to update. The options are On change, On change and every minute, and On change and every hour.
    • Click Save settings to apply the change.
  4. 4
    Use the General Settings to update your time zone. To make sure your formula displays the time correctly for your time zone, open the File menu and click Settings. In the Settings for this spreadsheet dialog box, click the General tab. Under Time zone, click the dropdown menu and select the correct time zone. When you’re done, click Save settings.[11]
    • You can also change your locale in the General Settings menu.
  5. Advertisement

Method 3
Method 3 of 4:
Keyboard Shortcuts (PC)

  1. 1
    Insert the time into a cell with Ctrl+ Shift+;. Click on the cell where you want to display the time and press Ctrl+ Shift+; on your keyboard. This will display the current time in the format HH:MM:SS AM/PM in that cell.[12]
    • When you insert a timestamp using this shortcut, the time is fixed. It will reflect the time when you entered it and will not update if you make changes to the sheet.
  2. 2
    Add the date using Ctrl+;. Use this shortcut to show just the date without the time in the selected cell. It will display the date in the format M/DD/YYYY.[13]
  3. 3
    Use Ctrl+Alt+ Shift+; to enter time and date. If you want to show both the time and date in a cell, select the cell and press the keys Ctrl+Alt+ Shift+;. This will display the time and date in the format M/DD/YYYY HH:MM:SS.[14]
  4. Advertisement

Method 4
Method 4 of 4:
Keyboard Shortcuts (Mac)

  1. 1
    Press Command+ Shift+; to enter the time in a cell. If you’re using Google Sheets on a Mac, select the cell where you want to display the time and press Command+ Shift+;. Just like on a PC, this will display a fixed timestamp in the format HH:MM:SS AM/PM.[15]
  2. 2
    Insert the date with Command+;. If you just want the cell to show the date and not the time, use the shortcut Command+;. The date will display in the format M/DD/YYYY.[16]
  3. 3
    Input date and time with Command+ Option+ Shift+;. This shortcut will show both the time and the date in the cell you selected, in the format M/DD/YYYY HH:MM:SS.[17]
  4. Advertisement

Community Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.


      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Thanks for submitting a tip for review!

      About This Article

      Co-authored by:
      wikiHow Staff Writer
      This article was co-authored by wikiHow staff writer, Megaera Lorenz, PhD. Megaera Lorenz is an Egyptologist and Writer with over 20 years of experience in public education. In 2017, she graduated with her PhD in Egyptology from The University of Chicago, where she served for several years as a content advisor and program facilitator for the Oriental Institute Museum’s Public Education office. She has also developed and taught Egyptology courses at The University of Chicago and Loyola University Chicago. This article has been viewed 1,674 times.
      How helpful is this?
      Co-authors: 4
      Updated: June 16, 2022
      Views: 1,674
      Thanks to all authors for creating a page that has been read 1,674 times.

      Is this article up to date?