41

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


39

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


30

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


21

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


17

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


17

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.


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


13

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


7

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


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


6

={DATE(RIGHT(A1, 4), MONTH(MID(A1, 5, 3)&1), REGEXEXTRACT(A1, "\d+")), TEXT(INDEX(SPLIT(A1, " "), 1, 4), "hh:mm:ss")} with timezone in account: ={DATE(RIGHT(A1, 4), MONTH(MID(A1, 5, 3)&1), REGEXEXTRACT(A1, "\d+")), TEXT(INDEX(SPLIT(A1, " "), 1, 4)+IFERROR( TIMEVALUE(JOIN(":", SPLIT(REGEXREPLACE(REGEXEXTRACT(A1, "\+(\d+)"), "(.{2})", "/$1"),"...


5

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.


5

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


5

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.


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

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

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

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

Notice: this answer does not work anymore as GoogleClock() has been replaced by the NOW() function. 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 ...


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

The documentation says this: The NOW Function and Worksheet Recalculation The NOW() function is a member of Google Sheet's group of volatile functions, which, by default, recalculate or update every time the worksheet in which they are located recalculates. For example, worksheets recalculate each time they are opened or when certain events ...


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

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'); }


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)


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