53

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


42

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


20

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

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


8

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.


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


8

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


7

={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"),"...


6

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.


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

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


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

As Amit said in his answer, the onEdit() trigger will work for this, and you'll need to get values from the Edit event object. A complete solution for this use case would be something like: function onEdit(e) { // Prevent errors if no object is passed. if (!e) return; // Get the active sheet. e.source.getActiveSheet() // Set the cell ...


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

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

This is another way of doing it. Formula =QUERY(TIMESTAMPS!A:B, "SELECT A, B ORDER BY A DESC LIMIT 10") Explained The QUERY formula needs the data from the timestamps sheet, for it to process. The result must contain column A and B, of which column A is sorted descendingly (new to old). Finally, the result is limited to 10 entries (rows). Screenshot ...


2

On a Brazilian portuguese keyboard use Ctrl+ç for current date Ctrl+Shift+ç for current time Ctrl+Alt+Shift+ç for current date and time


2

Another option is to copy the value of the cell and paste it in the same respective cell, but with values only special paste option. Input =NOW() in a cell. Copy it Ctrl+C Paste in the same cell Ctrl+Shift+V


2

This needs testing but something like this: change if( r.getColumn() != 2 ) { to: if( r.getColumn() == 2 && s.getName() == "Short Sale/REO Pricing" ) { test the above, then change: SpreadsheetApp.getActiveSheet().getRange('B' + row.toString()).setValue(time); to: SpreadsheetApp.getActiveSheet().getRange(row, s.getLastColumn())....


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