Sometimes, you may want to quickly insert a timestamp in Google Sheets to mark when the activity was done.
There are multiple ways of adding a timestamp in Google Sheets and in this tutorial, I will show you three such methods.
Each method has it’s pros and cons and I have listed these clearly.
Table of Contents
Keyboard Shortcut to Insert TimeStamp
If you want to quickly insert a timestamp in one cell (or a few cells), you can use the below keyboard shortcuts:
To Insert the current date
Control + : (hold the Control key and press the colon key)
To insert the current time
Control + Shift + : (hold the Control and Shift keys and press the colon key).
Note that these keyboard shortcuts would insert a static date and time value. This means that if you make any changes in the worksheet or close and open it, these date/time values will not change.
In case you want to get a combined value of date as well as time, you can first use these two keyboard shortcuts to insert the date and time and then simply add these two cells (as shown below).
Remember to convert the formula to value to make sure the combined timestamp (with date and time) is a static value (hint: use Paste Special to do this).
Formula to Insert TimeStamp
There are a couple of formulas that you can use to quickly insert the date and time in Google Sheets.
To insert the current date, use the below formula:
=TODAY()
To insert the current date as well as the current time, use the below formula:
=NOW()
Note that these formulas are volatile, which means that if you change any cell in the Google Sheets document, it will lead to a recalculation and make these formulas to update.
So if you use the TODAY function in a cell and open that Google Sheet document the next day, the formula will update to show you the current date.
In case you want these to be static values, you can convert the formula to value (or use the keyboard shortcuts covered above).
Also, the result of these formulas is a number (as all date and time values are stored as numbers in Google Sheets). This allows you to format the result and show it in different formats.
For example, if you have a date as 01-01-2020, below are some of the formats you can use to display it:
- 01 January 2020
- January 01, 2020
- 01/01/2020
- 01 Jan 2020
Similarly, with time, you can display it in the AM/PM format or in 12/24 hour format.
Automatically Insert Time Stamp in Google Sheets Using a Script
While both the above methods (keyboard shortcuts and formulas) work well, these would not automatically insert the timestamp for you.
For example, suppose you have a dataset where you’re tracking activities and you want a timestamp to be inserted as soon as an activity is added to a cell.
This can be done with a simple script in Google Sheets.
Below are the steps to use a script to automatically add timestamps in Google Sheets:
- Open the Google Sheets document
- Click on the Tools options in the menu
- Click the ‘Script Editor’ option. This will open the Script Editor in Google Sheets.
- In the script editor code window, copy-paste the following code (the 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()); } } }
- Save the code by clicking on the Save icon in the toolbar. It may ask you to give it a name.
Once you have this code in place, whenever you enter anything in any cell in Column A, a timestamp would automatically appear in the adjacent cell in Column B. Note that this timestamp is static, which means it won’t change when you make any change in the worksheet.
The above code only works on Sheet1 (as we have specified that in the third line. You can change the name “Sheet1” to whatever sheet you want this code to work on.
Also, the code has been created considering you are entering the data in column A and need the time stamp in Column B. You can modify these as required.
I hope you found this tutorial about inserting the timestamp useful!
You may also like the following Google Sheets tutorials:
32 thoughts on “3 Easy Ways to Automatically Insert Timestamps in Google Sheets”
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.
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)
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
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.
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
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?