I've got a spreadsheet in Google Drive and one column is meant for the date the entry is made. Is there a button to do this? I'm aware of the functions =NOW() and =TODAY() but their description says "the value is updated when the document recalculates". However I reopened the document a few minutes latter and it still had the same time. Why is this?
It would be even easier that when I start typing in a new row the column for the date auto-populates.
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.
Reference: Recalculation of Sheet
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 suitable for registering times. It does not even have "Paste values only".
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.
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 macros.
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 opening/refreshing the spreadsheet.
If you use:
=NOW()+GoogleClock()*0
you will have a granularity of one second, it will update with every edit, and be no less than one minute off when you open the spreadsheet. But it will also update every minute regardless of editing, which may not be desirable.
On a Brazilian portuguese keyboard use
On an italian keboard use
Further to @RasAlhague's excellent answer, I added a Menu to insert the date, by adding in this function:
function onOpen(){
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = []
menuEntries.push({name: "Insert current static date", functionName: "currentStaticDate"})
spreadsheet.addMenu("Insert current static date", menuEntries);
On a German keyboard use
On a Swedish Mac keyboard use ⌘ + D.
