Date and timestamp in Google sheets

by

Standard formulas like or are volatile, meaning they update every time you edit the sheet. To get a static date and time—one that doesn’t change once entered—you must use shortcuts, iterative calculation formulas, or scripts. [1, 2, 3, 4, 5]

1. Manual Shortcuts (Easiest)

Shortcuts are the most direct way to “take a snapshot” of the current time and insert it as a fixed value.

  • Date only:
    • Windows/ChromeOS: +
    • Mac: +
  • Time only:
    • Windows/ChromeOS: + +
    • Mac: + +
  • Date and Time:
    • Windows/ChromeOS: + + +
    • Mac: + + + [7, 8, 9, 10, 11, 12, 13, 14]

2. LAMBDA Formula (Automatic Trigger) [15]

You can use a function to capture a timestamp that only updates when a specific “trigger” cell (e.g., cell A1) is edited. Use this formula in the cell where you want the timestamp:

  • How it works: This formula uses a LAMBDA function
    to “freeze” the value of once the logical test () is met for the first time.

3. Iterative Calculation Formula (Circular Reference)

This method allows a cell to reference itself to check if it already contains a value. If it’s empty, it records the current time; if not, it keeps its current value.

  1. Enable iterative calculation: Go to File > Settings > Calculation and set Iterative calculation to On.
  2. Use this formula (assuming you are in cell and cell is the trigger): [1, 10, 23, 24]

4. Google Apps Script (Advanced)

For full automation (e.g., timestamping every time any row is edited), you can use

Google Apps Script

.

  • Go to Extensions > Apps Script and use an function to set the cell’s value to . This method is highly customizable and ensures the timestamp is truly permanent. [26, 27, 28, 29, 30]

AI responses may include mistakes.

[1] https://www.youtube.com/watch?v=fd-O3a9TQs4

[2] https://www.wikihow.com/Google-Sheets-How-to-Insert-Time-in-Cell-Automatically

[3] https://rows.com/blog/post/how-to-add-date-in-google-sheets

[4] https://golayer.io/blog/google-sheets/add-time-in-google-sheets/

[5] https://support.google.com/docs/thread/360144449/automatic-date?hl=en

[6] https://support.microsoft.com/en-gb/office/insert-the-current-date-and-time-in-a-cell-b5663451-10b0-40ab-9e71-6b0ce5768138

[7] https://www.ablebits.com/office-addins-blog/date-time-google-sheets/

[8] https://tiller.com/timestamp-google-sheets/

[9] https://www.ablebits.com/office-addins-blog/date-time-google-sheets/

[10] https://www.wikihow.com/Google-Sheets-How-to-Insert-Time-in-Cell-Automatically

[11] https://www.youtube.com/watch?v=FbhB7Q7qZJU

[12] https://support.google.com/docs/answer/181110?hl=en&co=GENIE.Platform%3DDesktop

[13] https://www.geeksforgeeks.org/google-sheets/how-to-add-time-google-sheets/

[14] https://infoinspired.com/google-docs/spreadsheet/insert-a-static-timestamp-in-google-sheets/

[15] https://www.youtube.com/watch?v=ngMc7e5C9Hg

[16] https://www.reddit.com/r/googlesheets/comments/1hxjuil/how_to_get_a_static_datetime_stamp_in_a_cell_when/

[17] https://www.thebricks.com/resources/guide-how-to-timestamp-on-google-sheets-using-chatgpt

[18] https://www.youtube.com/watch?v=ngMc7e5C9Hg

[19] https://www.reddit.com/r/googlesheets/comments/1hxjuil/how_to_get_a_static_datetime_stamp_in_a_cell_when/

[20] https://www.reddit.com/r/googlesheets/comments/1c064qp/time_stamp_formula_on_google_sheets/

[21] https://www.youtube.com/watch?v=fd-O3a9TQs4

[22] https://techcommunity.microsoft.com/discussions/excelgeneral/record-time-of-entry/1525271

[23] https://www.reddit.com/r/googlesheets/comments/uidfp0/want_an_automated_static_datetime_which_doesnt/

[24] https://learn.microsoft.com/en-us/answers/questions/5218404/automatic-date-and-time-formula-resetting-to-base

[25] https://stackoverflow.com/questions/71319387/timestamp-in-cell-when-any-cell-in-row-is-edited

[26] https://www.youtube.com/watch?v=FbhB7Q7qZJU

[27] https://www.youtube.com/watch?v=FbhB7Q7qZJU

[28] https://stackoverflow.com/questions/67564661/static-timestamping-in-google-sheets

[29] https://www.youtube.com/watch?v=h-opwF6V4go

[30] https://www.thebricks.com/resources/how-to-insert-dates-in-google-sheets-automatically