I use PSPad as a text editor, which allows you to press Alt + D to insert a timestamp, e.g.:
2010-07-17 23:45:44
Is there a way to do this in a Google Spreadsheet?
asked Jul 17, 2010 at 21:46
-
4This would be helpful in using a Google document as something like an engineering log. +1.– Thomas OwensCommented Jul 17, 2010 at 22:54
-
1yes, I use google docs as external datasources for websites and would like to be able to timestamp for this reason– Edward TanguayCommented Jul 17, 2010 at 23:17
-
1@EdwardTanguay if you by any chance passing by.. you should change the correct answer to the most voted one :)– LipisCommented May 9, 2015 at 20:39
-
1Readers are advised to use the best answer, even though it is not currently on top.– user135384Commented Jul 18, 2017 at 20:21
Add a comment
|
11 Answers
I use AutoHotKey to perform this function.
AutoHotKey is a Windows scripting application and language.
The code I use is below, it would be easily modifiable to insert the time and change the slashes to hyphens if that is what you prefer.
+!d:: ;Shift-Alt-D: Insert current date
SendInput %A_DD%/%A_MM%/%A_YYYY%
return
answered Jul 18, 2010 at 0:18
-
yes, autohotkey is my friend, odd I forgot about it for this, I used %A_YYYY%-%A_MM%-%A_DD% %A_Hour%:%A_Min%:%A_Sec% and it works perfectly, thanks! Commented Jul 18, 2010 at 8:32
-
Google Sheets supports the following keyboard shortcuts:
Ctrl + Shift + : is the keystroke to insert time.
Ctrl + ; is the keystroke to insert date.
Ctrl + Alt + Shift + : is the keystroke to insert date and time.
See Keyboard accelerators, mnemonics, and shortcuts for more details.
-
4This is similar to Microsoft Excel, however, unlike Excel you can't seem to follow one shortcut with another in order to produce a complete timestamp. The shortcut in Google Docs only seems to work when the entire cell is selected, not when it is being edited, so you can only get the date OR the time with this method, not both.– MrWhiteCommented Jul 9, 2012 at 14:21
-
3Nice! Note, this only works for Spreadsheets, not Documents.– jimmyorrCommented Sep 7, 2012 at 17:26
-
Not working on firefox linux :-( is there a way to get this function through menus??!! Commented May 11, 2018 at 11:55
-
1Don't know when this shortcut was added on the Mac side (been using Command-semicolon to do the date for the past few years), but Command-Alt-Shift-colon does insert the full timestamp for MacOS+Chrome. Assuming the aforementioned Windows shortcuts also work, sans the use of AutoHotKey, this should be the accepted answer– dancowCommented Mar 26, 2020 at 14:53
-
It is documented in the Google Spreadsheets help section, but does not seem to work, alas... I used the NOW() copy-paste trick instead (see other section). Commented Mar 28, 2020 at 10:15
I created a little script that converts the string literal "_now" to current datetime -
function onEdit(e) {
if (e.range.getValue() == "_now") {
e.range.setValue(new Date());
}
}
I find it pretty handy to type _now and have it convert to current datetime value.
See https://developers.google.com/apps-script/quickstart/macros and https://developers.google.com/apps-script/understanding_events for more info on how to add this to your Google Spreadsheet
-
1
-
4This one should be the answer if considering across all platforms not just for windows.– RobertCommented Dec 24, 2014 at 15:10
-
@Robert, Isn't webapps.stackexchange.com/a/24692/10579 a better answer?– PacerierCommented May 24, 2015 at 20:39
-
@Pacerier I don't think so as these short cuts only add the date or time. Not the date/time the OP asked for. Read the comments made at the link you provided. Commented Aug 7, 2015 at 13:59
-
The formatting of timestamps created with this script is addressed here.– user79865Commented Aug 3, 2016 at 1:12
You can put in a bookmarklet with this location:
javascript:var thetime=new Date();var txtNode=document.createTextNode((thetime.getMonth()+1)+'/'+thetime.getDate()+'/'+thetime.getFullYear()+' '+thetime.getHours()+':'+thetime.getMinutes()+':'+thetime.getSeconds()); var myInputNode=document.getElementsByClassName('cell-input')[1]; if (myInputNode.hasChildNodes()) { myInputNode.replaceChild(txtNode, myInputNode.childNodes[0]); } else { myInputNode.appendChild(txtNode); }; void(0);
then edit the cell and click the bookmarklet.
The formula for current date-time is =now(). This can be used to create a static timestamp as follows:
- Enter
=NOW()in a cell - Copy this value with CtrlC
- Paste into the same cell with CtrlShiftV (or equivalents for "paste text only" on other platforms)
-
3That's not a timestamp though, is it? It's just showing the current date/time when the spread sheet was opened, no?– aleCommented Jan 9, 2013 at 1:31
- In a nearby cell, or within a frozen pane, input
=today()into a cell to get the current date. - Copy the cell, and then use (Paste special → Paste values only) from the edit menu, to get a static date and time, at the time that you pasted the value in the cell that you wanted the date stamp.
-
Equivalently, use Ctrl-C followed by Ctrl-Shift-V (without reaching the menu). The same approach can be used with
=now()to get both date and time within the same cell: this is arguably the easiest way to do this without a script.– user79865Commented Jul 22, 2015 at 16:04
Google Sheets doesn't support it, but you can try another workaround without external apps:
- Insert a comment (Ctrl + M)
- Copy the TimeStamp from there (Ctrl + C or Ctrl + X)
- Delete the comment (Right click > Delete)
- Paste the TimeStamp (Ctrl + V)
(If you are on a MacOS, use Cmd instead of Ctrl for Copy/Cut/Paste, but Ctrl + M for inserting the comment, because Cmd + M is minimizing the window.)
You can refer this tutorial, it contains video too. In the script code, change
var timestamp_format = "MM-dd-yyyy"; // Timestamp Format.
By default it only gives you date, change to below code to get time.
var timestamp_format = "MM-dd-yyyy hh:mm:ss"; // Timestamp Format.
Is this what you are looking for ? I tried it and it works perfectly.
I also created a script for this, and I wanted a format that isn't available in the default timestamps (such as in =TODAY() or =NOW()).
It also grabs the timezone based on the current session (which is set in the Sheet's preferences).
function _NOW(input)
{
// for formatting, see:
// https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
var timestamp_format = "yyyy-MM-dd hh:MM a"; // Timestamp Format eg: 2019-04-11 12:04 PM
var timezone = Session.getScriptTimeZone();
var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
return date;
}
Using it is easy:
=_NOW()
To clarify: I did the date formatting manually because the custom date formatting in Google Sheets is a pile of flaming trash (no way to set a custom format as the default, no way to change the order of the date/time elements without deleting everything first and adding each one part at a time).
-
-
@nhed every time this script is called (such as when you open the spreadsheet) it will return the current date in that format as a string. If you want a timestamp that's permanent, never updating, you might put this in a cell and copy the value from that cell, then paste it where you want it. Commented May 1, 2019 at 20:30
-
I added the script on my desktop browser and the spreadsheet was already open on my phone... it still was producing on my phone the same timestamp that was on my desktop... requiring that I close and reopen to add a data point doesn't seem practical... I combined your formatting with a different function above to get something that worked for me– nhedCommented May 2, 2019 at 8:50
=if(len(A2)>0;if(B2<>"";B2;now());"")
In Settings, change the circuit solution to 1, and it will work without JavaScript
tanguay, I have just tested this =IF(A2<>"",NOW(),""). If A2 has a value then it enters the timestamp. If A2 is empty then the timestamp is not entered in the timestamp column.
