4 Easy Ways to Add the Time and Date Automatically in Sheets

PDF download Download Article
PDF download 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

PDF download Download Article
  1. 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. 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.
    Advertisement
  3. 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. 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 */ 
 function 
 onEdit 
 ( 
 e 
 ){ 
 const 
 sh 
 = 
 e 
 . 
 source 
 . 
 getActiveSheet 
 (); 
 sh 
 . 
 getRange 
 ( 
 'M' 
 + 
 e 
 . 
 range 
 . 
 rowStart 
 ) 
 . 
 setValue 
 ( 
 new 
 Date 
 ()) 
 . 
 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. 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. 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. 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

PDF download Download Article
  1. 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. 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. 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. 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)

PDF download Download Article
  1. 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. 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. 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)

PDF download Download Article
  1. 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. 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. 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

Expert Q&A

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

      Video

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      How to
      Delete Google Photos and Keep Them on Your iPhone
      How to
      Restore Google Authenticator if You Lost or Reset Your Phone
      How to
      Sign Up for a New Google Voice Phone Number
      How to
      Disable Google Safe Search
      Dark Mode in Google Docs: Easy Steps for Desktop & Mobile
      2 Easy Ways to Permanently Delete (or, Hide) Your YouTube Channel
      How to
      Sign Out of Your Google Account on All Devices at Once
      2 Simple Ways to Unsync Google Photos
      Google Docs: 4 Ways to Delete Extra Pages in Your Document
      How to
      Use Google Translate: Complete Beginner's Guide
      How to
      Fix the "Google Play Store Has Stopped" Error
      Reverse Image Search in Google Lens: Android, iPhone, & Desktop
      How to
      Step Seeing Google Ads on PC or Mobile Devices
      2 Ways to Write a Review on Google
      Advertisement

      References

      1. ↑ https://developers.google.com/apps-script/guides/bound
      2. ↑ https://www.computing.net/office/google-sheets-timestamp
      3. ↑ https://www.computing.net/office/google-sheets-timestamp
      4. ↑ https://www.computing.net/office/google-sheets-timestamp
      5. ↑ https://www.computing.net/office/google-sheets-timestamp
      6. ↑ https://www.computing.net/office/google-sheets-timestamp
      7. ↑ https://www.computing.net/office/google-sheets-timestamp
      8. ↑ https://support.google.com/docs/answer/3094139?hl=en#null
      9. ↑ https://www.computing.net/office/google-sheets-timestamp
      1. ↑ https://support.google.com/docs/answer/58515
      2. ↑ https://support.google.com/docs/answer/58515
      3. ↑ https://support.google.com/docs/answer/181110
      4. ↑ https://support.google.com/docs/answer/181110
      5. ↑ https://support.google.com/docs/answer/181110
      6. ↑ https://support.google.com/docs/answer/181110
      7. ↑ https://support.google.com/docs/answer/181110
      8. ↑ https://support.google.com/docs/answer/181110

      About This Article

      In other languages
      Thanks to all authors for creating a page that has been read 179,540 times.

      Is this article up to date?

      Advertisement