Is there any way to create a timestamp in a Google Spreadsheet?

Question

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?

score 54 · Answer 1 · edited Jul 18 '17 at 20:18

54

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.

edited Jul 18 '17 at 20:18

answered Mar 13 '12 at 18:40

Danniogirl

541
1
4
2

score 14 · Answer 2 · answered May 20 '13 at 08:21

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

score 12 · Accepted Answer · edited Mar 13 '12 at 18:49

12

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

edited Mar 13 '12 at 18:49

Alex

22,820
12
83
106

answered Jul 18 '10 at 00:18

tobeannounced

2,793
2
22
34

score 7 · Answer 4 · edited Apr 05 '12 at 15:34

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.

score 3 · Answer 5 · edited Jul 22 '15 at 16:10

3

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)

edited Jul 22 '15 at 16:10

answered Jan 08 '13 at 21:48

jennifer

41
1

score 1 · Answer 6 · edited Apr 04 '12 at 20:50

1

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.

edited Apr 04 '12 at 20:50

Alex

22,820
12
83
106

answered Apr 04 '12 at 20:27

Tristan Parlette

19
1

score 1 · Answer 7 · edited May 09 '15 at 18:46

1

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

edited May 09 '15 at 18:46

Rubén - Volunteer Moderator -

46,305
18
101
297

answered Jul 21 '10 at 20:08

Lipis

9,798
1
35
43

score 1 · Answer 8 · answered Aug 07 '15 at 13:53

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.

Kyle Falconer · Answer 9 · 2019-04-11T21:19:21.517

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

score -2 · Answer 10 · edited Apr 11 '19 at 22:29

-2

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

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

edited Apr 11 '19 at 22:29

jonsca

1,732
18
23
44

answered Apr 11 '19 at 07:47

Vratislav Novák

1

score -2 · Answer 11 · answered May 31 '19 at 14:50

-2

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.

answered May 31 '19 at 14:50

Howard Riminton

1

Is there any way to create a timestamp in a Google Spreadsheet?

11 Answers11

Linked

Related