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.
Our straightforward timestamp Google Sheets tutorial reveals quick and easy hacks – so let’s get started!
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: 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.
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
Several formulas can be used to insert timestamps in Google Sheets.
The following formulas are volatile. If the Sheets cell is changed, it will recalculate and update these formulas. 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
Inserting Current Date & Time
To insert the current date as well as the current time, use the below formula:
=NOW()
Here’s how to use the 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.
Timestamp Google Sheets Script
While both keyboard shortcuts and formulas work well, these won’t automatically insert timestamps in Google Sheets.
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.
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 this 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 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());
}
}
}
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
Take Your Google Sheets Skills to the Next Level
We hope that our timestamp Google Sheets tutorial has been useful! If you’re looking to expand your knowledge of spreadsheets, we’ve got you covered:
- How to Insert Bullet Points in Google Sheets
- How to Insert Checkboxes in Google Sheets
- How to Determine Word Count in Google Sheets
Otherwise, why not consider joining one of Coursera’s thousands of accredited certification and degree courses? It’s one of the easiest ways to improve your knowledge and skills!
56 thoughts on “A Simple Timestamp Google Sheets Tutorial for 2023”
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!
Awesome the script editor work so well for me, thanks
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);
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
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?
Script editor is no longer available.
You have to navigate to Extensions > Script editor now instead.
Hi,
can I make the timestamp appear in a different sheet? How can I define which cell to appear in?
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());
}
}
}