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.


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.


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


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


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


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.


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


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


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


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


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


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


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


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


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


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


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


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


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.


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


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


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


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")) }


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


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.


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.


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


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