43

All date/time values in spreadsheets are internally handled as floating point values. To add 3 hours to a date/time just add (3/24) to the original date/time. =F3+(3/24) This also works in MS Excel.


39

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.


36

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


33

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


20

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


16

See also: Convert a date in Google Spreadsheet? =A1+Time(3,0,0) Adds 3 'Hours', 0 'Minutes', 0 'Seconds' to the date/time value of A1 Additionally if only you use the main sheet you can go into the sheet settings and change it's timezone accordingly.


14

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


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

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


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


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


7

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


6

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() === ''...


5

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


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


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

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

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


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.


3

This line: if( nextCell.getValue() !== '' ) //is empty? Should be: if (nextCell.getValue() === 'Complete') { nextCell.setValue(new Date()).setNumberFormat('dd-MM-yyyy HH:mm:ss'); }


3

All you need is this short script function getDate() { return new Date(); } And then you can put this equation into E1: =getDate(C1:D1) which you can then drag down. You may also want to change the format for column E and select a date format where both date and time are displayed.


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