Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
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.


35

Ok, here is a modified version of the code in my previous answer: function onEdit(e) { // Your sheet params var sheetName = "MySheet"; var dateModifiedColumnIndex = 2; var dateModifiedColumnLetter = 'B'; var range = e.range; // range just edited var sheet = range.getSheet(); if (sheet.getName() !== sheetName) { return; } // If the ...


22

ctrl+; inserts current date ctrl+shift+; (ctrl+:) inserts current time ctrl+alt+shift+; (ctrl+alt+:) inserts current date and time Use ⌘ instead of ctrl on Mac OS However it does not work in Google Sheets mobile app. Seems that mobile app has no similar functionality. =now() updates to current date and time when any edit is made to spreadsheet so it's not ...


18

You can try adding a Google Apps Script to capture when a cell is edited and add a timestamp to a different cell. Here's a previous answer that is similar: Google Spreadsheet Timestamp? function onEdit() { var s = SpreadsheetApp.getActiveSheet(); if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet var r = s.getActiveCell(); if( ...


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


12

=iferror(A1+B1+C1+D1+J1+K1+L1+"x",today()) whenever the cells in the row changes the formula re-calculates. with the "x" in the equation it guarantees an error. which in return, returns the date.


10

I was able to reproduce your findings, however, opening a spreadsheet doesn't result in a re-calculation. Press Ctrl + R to recalculate the entire sheet. There's one concern about this method: it changes all NOW() formula's into the current date/time. Therefore you might want to consider pressing Ctrl + ; in the creation date cell and add the date manually....


10

Try Shift+Ctrl+: and Ctrl+; Those are the keyboard shortcuts for "insert time" and "insert date" respectively. (based on pnuts' comment)


8

FYI, the accepted answer uses DD as the day format, which will get you something like "312", i.e. the 312th day of the year. I used this: "yyyy-MM-dd, hh:mm:ss" to get this: 2013-11-12, 03:43:20


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


5

Try this answer from StackOverflow: Here's the relevant code (edited): function onEdit() { var s = SpreadsheetApp.getActiveSheet(); if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet var r = s.getActiveCell(); if( r.getColumn() == 13 ) { //checks the column var nextCell = r.offset(0, 1); if( nextCell.getValue() === ''...


4

I hope this is OK to put here. Below is a modification of the function above but to do it for a row (row 9) rather than columns. Figuring out how to use the column assignment was very frustrating but ended up being extremely simple. Thanks to OnenOnlyWalter for the original code: function onEdit() { var s = SpreadsheetApp.getActiveSheet(); // Get ...


4

I made an adjustment to Vidar's script for anyone who only wants the autodate to work once (the first time a cell is changed). This may be helpful in a timesheet where users sometimes go back to add hours in the past and won't want the date updated to the current time. Note: For my version, I changed the range to column B, rather than D. //----------------...


4

This answer is coming late to the party, but you can achieve what you want by installing this script: function onEdit(event) { var eventRange = event.range; if (eventRange.getColumn() == 4) { // 4 == column D var columnARange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 1, eventRange.getNumRows(), 1); var values = columnARange....


4

You could add a trigger that runs when new rows are added to the spreadsheet: function onModification(event) { var sheet = event.source.getActiveSheet(); if (event.changeType == "EDIT") { // A cell is edited. Other values include "INSERT_ROW" (when a new row is inserted) fillEmptyRows(sheet); } } function fillEmptyRows(sheet) { var range = ...


4

This is an old post, but I was looking into this recently and figured I'd chime in. Depending on you needs, the easiest answer I could think of is by creating a drop down menu and referencing a function on another cell. Example: Select A10:A20 > right click > Data Validation Criteria = "List from range" > F2 Formula in cell F2: =mult(now(),1) To ...


3

Code I've reduced your code to the following: function onEdit(e) { var sName = e.source.getActiveSheet().getName(); if(sName != 'Image Timeline and Status') { var s = e.source.getActiveSheet(); var c = s.getActiveRange().getColumnIndex(); var r = s.getActiveRange().getRowIndex(); if(c == 8 || c == 9 || c == 10) { var username = ...


3

I think the question is how to get today's date into a spreadsheet cell so that remains unchanged over time. I put "=today()" into a frozen header cell one time. That changes as the date changes. When I need today's date, I copy that cell and Paste special>Paste value only into the desired cell.


3

Another option to consider is =GoogleClock(). This function differs somewhat from =NOW() in that it has a granularity of one minute (rather than one second), but it is guaranteed to update each and every minute, and be correct (to the nearest whole minute) on opening/refreshing the spreadsheet. If you use: =NOW()+GoogleClock()*0 you will have a ...


3

See if this helps ? function onEdit(e) { var s = e.source.getActiveSheet(), watchCols = [2, 5], offsetCol = [8, 10], ind = watchCols.indexOf(e.range.columnStart) if (s.getName() !== "Sheet1" || ind === -1) return; e.range.offset(0, offsetCol[ind]) .setValue(!e.value ? null : Utilities.formatDate(new Date(), "GMT", "hh:mm")) }


3

Short Answer Replace if( r.getColumn() != 5 ) by if( r.getColumn() != 5 && r.getRow() != 1 ) . Long answer && is the AND operator r.getRow() != 1 compares the row of the edited cell with the row number that you want to be skipped. Full code minimal modification function onEdit() { var s = SpreadsheetApp.getActiveSheet(); var r = s....


3

There is a shortcut exactly for this: Select the cell Press CTRL and ; That's it, the cell's content has been replaced with today's date.


3

Get the sheet that was edited. Change code to: var sheet, ss; ss = SpreadsheetApp.getActiveSpreadsheet(); sheet = ss.getActiveSheet();//Get the active sheet Now the sheet variable is the active sheet that was just edited. So anything done with sheet will affect that sheet.


2

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)


2

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.


2

To insert static DateTime value use macros: In your sheet Tools -> Script Editor. Paste this code: function currentStaticDate() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getCurrentCell().setValue(new Date()); }; Save and return to your sheet. Import script with Tools -> Macros -> Import. Add shortcut with Tools -> Macros -> Manage ...


2

function onEdit() { var s = SpreadsheetApp.getActiveSheet(); if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet var r = s.getActiveCell(); if( r.getColumn() == 1 ) { //checks the column(A) var nextCell = r.offset(0, 1); //offset to same row, and column B; A+1 if( nextCell.getValue() === '' ) { //is empty? var ...


2

This script checks if column B is updated, then updates the last column in that row with a timestamp: function onEdit(event) { var sheet = event.source.getActiveSheet(); // note: actRng = the cell being updated var actRng = event.source.getActiveRange(); var index = actRng.getRowIndex(); var cindex = actRng.getColumnIndex(); if (cindex == 2) {...


Only top voted, non community-wiki answers of a minimum length are eligible