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.
Table of Contents
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 :
- Insert the current date: Control + :
- Insert the current time: Control + Shift + :
- Insert the current date and time: Control + Alt + Shift + ;
Timestamp Shortcuts for Mac
- Insert the current date: Command + :
- Insert the current time: Command + Shift + :
- 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).
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()
Here’s how to use the formula:
- Click on the cell you want to add a timestamp to.
- Type =Today or select it from the formula suggestions.
- 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()
Here’s how to use the NOW formula:
- Click on the cell you want to add a timestamp to.
- Type = Now or select it from the formula suggestions.
- 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.
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:
- Open the Google Sheets document.
- Click on Tools > Script Editor.
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());
}
}
}
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:
- How to Insert Bullet Points in Google Sheets
- How to Insert Checkboxes in Google Sheets
- How to Determine Word Count in Google Sheets
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.
75 thoughts on “A Simple Timestamp Google Sheets Tutorial for 2024”
Thanks. it works for me…
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)?
Life saver. Thanks a lot bro
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.
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.
…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()
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.
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
Is there a way to edit this script so that it can work for a google sheet that has multiple sheets?
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
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 🙂
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.
is that possible?
Is it possible to do this for a set range? (i.e if Range K5:K21 is edited L5:L21 is timestamped)
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)
How can I add multiple columns into the script? For example, to look at A4:G35
Just drag down the formula in G sheets perhaps?
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())
Thank you. it work
Thank you!
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.
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());
}
}
}
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.
Thank you for this script! It works as advertised. But can the timestamp column auto populate when the entire row’s data is being pulled from a slack form?
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
}
What to write in a script, that in addition to the timestamp, i could also see the email -who made the changes.
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
}
}
How can you add multiple columns?
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!
Awesome the script editor work so well for me, thanks
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.
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);
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.
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());
}
}
Please, I would like to how can I change the Entering and Adjacent column as I want.
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.
Jeanette, did you solve this issue? If so, can you share how you did it, as I`m having the same issue
Add “//” before if( nextCell.getValue() === ” )
it will overwrite and not check if the cell is blank
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?
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?
Is there a way to do this over multiple sheets?
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
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
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
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.
[ CTRL+ALT+SHIFT+: ] will give you the date and time that will remain static in your sheet.
Thanks Petrus. That’s the only way it works for me on a non-US keyboard.
Thank you
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?
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.
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
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. :-\
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?
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.
is there a way to only insert the TIME and not the full DATE?
You can use the NOW() function and then format the cell to only show the time in Format > Number > Custome time and date
Works very nice!
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?
Getting this error when I save:
Syntax error: SyntaxError: Invalid or unexpected token line: 3 file: Code.gs
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.
the easiest way is to use the TODAY() function
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.
Hi,
can I make the timestamp appear in the same column but different row, meaning can I define which cell to appear in?
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
Try something like:
=IFERROR(INDEX($A$2:$A$100, SMALL(IF($C$2:$C$100<>0, ROW($C$2:$C$100)-ROW($C$2)+1), ROW(1:1))), "")
Script editor is no longer available.
You have to navigate to Extensions > Script editor now instead.
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.
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());
}
}
Hi,
can I make the timestamp appear in a different sheet? How can I define which cell to appear in?
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.
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)); }
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?
thank you so much.. works amazingly at 1st try
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());
}
}
}