25

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.

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.

Reference: Recalculation of Sheet

  • So the difference between Ctrl+R and Ctrl+; is +R updates all the NOW() and +; does not? – Celeritas Jul 24 '13 at 19:21
  • 1
    Ctrl+R gives you a solution to your problem, but it might create one at the same time. That's why I added the Ctrl+; because this adds the date "physically". – Jacob Jan Tuinstra Jul 24 '13 at 19:30
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 suitable for registering times. It does not even have "Paste values only".

  • 2
    This doesn't work on international keyboards. – Rubén Apr 20 '18 at 16:07
  • @Rubén checked now, it works in Russian keyboard layout (usually keybidings don't work in that layout so I have muscle memory to switch to en-US layout after typing each chunk of cyrillic chars, but it works in Google Docs). Chrome 66.0.3359.106, Mac OS 10.13.4. – kolen Apr 20 '18 at 20:25
  • @Rubén I know it's really late but in my Spanish keyboard the commands work if you replace the semicolon with ñ on MacOS and that inverted tilde ` to the right of the P key on Windows. So it would be Ctrl + Alt + Shift + ` to insert date and time (Cmd + Alt + Shift + ñ on MacOS) – Pablo Albornoz Feb 26 at 19:56
  • 1
    Also, you can't be editing the selected cell (with the cursor), but just have the cell highlighted (without the cursor and only the border) in order to use these commands. – John Sep 1 at 3:56
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 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.

  • GoogleClock isn't available anymore. – Rubén Feb 26 at 21:54
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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.