31

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?

  • 4
    This would be helpful in using a Google document as something like an engineering log. +1. – Thomas Owens Jul 17 '10 at 22:54
  • 1
    yes, I use google docs as external datasources for websites and would like to be able to timestamp for this reason – Edward Tanguay Jul 17 '10 at 23:17
  • 1
    @EdwardTanguay if you by any chance passing by.. you should change the correct answer to the most voted one :) – Lipis May 9 '15 at 20:39
  • 1
    Readers are advised to use the best answer, even though it is not currently on top. – user135384 Jul 18 '17 at 20:21

11 Answers 11

8

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
  • 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! – Edward Tanguay Jul 18 '10 at 8:32
  • No worries, glad I reminded you :) – tobeannounced Jul 20 '10 at 22:28
37

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.

  • 4
    This 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. – MrWhite Jul 9 '12 at 14:21
  • 3
    Nice! Note, this only works for Spreadsheets, not Documents. – jimmyorr Sep 7 '12 at 17:26
  • 1
    @Rubén Now there is one for both! (h/t kolen and Cameron Crest) – user135384 Jul 18 '17 at 20:18
  • Not working on firefox linux :-( is there a way to get this function through menus??!! – Philippe Gachoud May 11 '18 at 11:55
13

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
    Power to Google Apps Script – Jacob Jan Tuinstra May 20 '13 at 8:56
  • 4
    This one should be the answer if considering across all platforms not just for windows. – Robert Dec 24 '14 at 15:10
  • @Robert, Isn't webapps.stackexchange.com/a/24692/10579 a better answer? – Pacerier May 24 '15 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. – Jacob Jan Tuinstra Aug 7 '15 at 13:59
  • The formatting of timestamps created with this script is addressed here. – user79865 Aug 3 '16 at 1:12
6

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.

  • How do you put a bookmarklet? – Ruby Jun 26 '18 at 15:05
2
  1. In a nearby cell, or within a frozen pane, input =today() into a cell to get the current date.
  2. 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. – user79865 Jul 22 '15 at 16:04
2

The formula for current date-time is =now(). This can be used to create a static timestamp as follows:

  1. Enter =NOW() in a cell
  2. Copy this value with CtrlC
  3. Paste into the same cell with CtrlShiftV (or equivalents for "paste text only" on other platforms)
  • 2
    That's not a timestamp though, is it? It's just showing the current date/time when the spread sheet was opened, no? – ale Jan 9 '13 at 1:31
1

Google Sheets doesn't support it, but you can try another workaround without external apps:

  1. Insert a comment (Ctrl + M)
  2. Copy the TimeStamp from there (Ctrl + C or Ctrl + X)
  3. Delete the comment (Right click > Delete)
  4. 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.)

1

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).

  • This ended up being the same time for every invocation – nhed May 1 at 20:20
  • @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. – Kyle Falconer May 1 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 – nhed May 2 at 8:50
0

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.

0

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.

-1

=if(len(A2)>0;if(B2<>"";B2;now());"")

In Settings, change the circuit solution to 1, and it will work without JavaScript

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.