🤣🙃🤕 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.
🤣🙃🤕 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 1Method 1 of 4:Script Editor
- 1Click on . 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. XResearch source
- 2Select . 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
- 3Give 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.XResearch source
- 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."
- 4Paste 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:XResearch source
- 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.
- 5Make 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
- 6Click . 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.XResearch source
- 7Enter 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.XResearch source
Method 2Method 2 of 4:Time and Date Formulas
- 1Insert 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.XResearch source
- 2Use 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.XResearch source
- If you prefer to display the time using a 24-hour clock instead of a 12-hour clock, capitalize HH in the formula.XResearch source
- 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" ).
- 3Change how often the timestamp updates in . 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.XResearch source To do this:XResearch source
- 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.
- 4Use 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.XResearch source
- You can also change your locale in the General Settings menu.
Method 3Method 3 of 4:Keyboard Shortcuts (PC)
- 1Insert 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.XResearch source
- 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.
- 2Add 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.XResearch source
- 3Use 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.XResearch source
Method 4Method 4 of 4:Keyboard Shortcuts (Mac)
- 1Press ⌘ 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.XResearch source
- 2Insert 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.XResearch source
- 3Input 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.XResearch source