How can I lock the today() function in Google Sheets

user_14190046 0
Viewed 1623

Say I want to just capture the date at the moment when I input sth. to the spreadsheet. The TODAY function will capture the current date, but it will also automatically update itself tmr.

How could I make the TODAY function become a constant once the current time is captured?

google-sheets
2 Answers

Answer:

The today() function will always update every time you open the Sheet. In order to keep the date constant, you will either need to use an attached script, or using one of the keyboard shortcuts for Google Sheets.

The Apps Script Method:

You can create a custom function which is attached to your sheet by following the Tools > Script editor menu item. This will open a new window with a script editor in it. Here, you can create a function as follows:

function todaysDate() {
  var td = new Date();
  return (td.getMonth() + 1) + "/" + td.getDate() + "/" + td.getFullYear();
}

Save the script with the save icon, and go back to the Sheet.

You can now use this as a formula in your sheet - entering =todaysDate() in a cell will return the date in MM/DD/YYYY format.

  • Example: 8/31/2020.

NB: You can change the order of how the date is displayed by switching around the td.getMonth() + 1, td.getDate(), and td.getFullYear()sections of thereturn` line, depending on which format you wish to use.

The Keyboard Shortcuts Method:

As per the documentation on Google Sheets keyboard shortcuts:

  • The specific shortcut for adding today's date to a cell is Ctrl + ;.
    • Example: 8/31/2020
  • You can also use Ctrl + Alt + Shift + ; to add the date and time.
    • Example: 8/31/2020 14:02:56.

Things to note:

  • You can see a list of keyboard shortcuts in Google Sheets by pressing Ctrl + / on Windows and Chrome OS, or āŒ˜ + / on Mac.
  • Some shortcuts might not work for all languages or keyboards.
  • You can see a full list of keyboard shortcuts here.

References:

user_115514680

Disclaimer: I'm not sure what I'm doing.

I found a way around formatting so the date shows as it's formatted in my spreadsheet,

2 Feb 2022

Whenever I changed the order or removed the "/", it would return the following,

7/17/1905

Which is 42569 days away from today's actual date. So, I basically added that difference to it,

return (td.getDate() + td.getMonth() + td.getFullYear() + 42569);

I hope that sticks for the long run, and hope it helps you too.

user_181032400
Related