Google Sheets Add Timestamp When a Cell Changes

Yes. To add a fixed timestamp when a cell changes in Google Sheets, use a small Apps Script attached to the spreadsheet; a formula such as NOW() updates when the sheet recalculates, so it is not a dependable record of when a particular edit happened. For example, a script can write the latest edit time in column B whenever someone changes the corresponding cell in column A.

What a change timestamp records

A timestamp is useful when a sheet tracks requests, approvals, inventory, assignments, or follow-up work. If A2 contains a task status and B2 contains its timestamp, changing A2 from “New” to “In progress” can place the time of that edit in B2. The script below records the most recent qualifying change, not the original creation time or a full history of every change.

That distinction matters. If you need to know who changed a value, what it was before, or every edit over time, one timestamp cell is not an audit log. Google Sheets offers version history, and a custom script can be designed to append change records to a separate log, but those are different approaches with different setup and privacy considerations.

Set up a timestamp with Apps Script

In the spreadsheet, choose Extensions > Apps Script. In the script editor, replace any starter code with the following, then save the project. This example watches column A, skips the header row, writes a timestamp to column B, and clears the timestamp if the cell in column A is emptied. It also handles a pasted range that includes column A.

function onEdit(e) {
  // Ignore edits that do not include column A.
  const range = e.range;
  const firstColumn = range.getColumn();
  const lastColumn = firstColumn + range.getNumColumns() - 1;
  if (firstColumn > 1 || lastColumn < 1) return;

  // Keep the header row out of the timestamp records.
  const firstRow = range.getRow();
  const lastRow = firstRow + range.getNumRows() - 1;
  if (lastRow < 2) return;
  const startRow = Math.max(firstRow, 2);
  const rowCount = lastRow - startRow + 1;

  // Timestamp nonblank values in A; clear the timestamp for blanks.
  const values = range.getSheet()
    .getRange(startRow, 1, rowCount, 1)
    .getValues();
  const timestamps = values.map(([value]) => [
    value === "" ? "" : new Date()
  ]);
  range.getSheet()
    .getRange(startRow, 2, rowCount, 1)
    .setValues(timestamps);
}

Return to the sheet and edit a value in A2 or below. The first time you use a script that requires authorization, Google may show an authorization prompt; this simple onEdit trigger uses the edited spreadsheet and does not need you to run the function manually. Keep column B reserved for the timestamps, because the script writes there and will replace anything already in the affected cells.

How the onEdit trigger works

The function name onEdit is a simple trigger recognized by Apps Script. Google runs it when a user changes a cell’s value in the spreadsheet interface. The edit event, represented by e in the code, supplies the edited range. The script checks whether that range overlaps column A, reads the relevant values, and writes date-and-time values into column B.

The script stores a date value rather than text. That makes the result sortable and usable in date calculations. To control how it looks, select the timestamp cells and choose Format > Number > Custom date and time. A format such as yyyy-mm-dd hh:mm:ss makes seconds visible; a shorter date-and-time format is easier to scan but may conceal differences between rapid edits. The displayed time follows the spreadsheet’s time-zone settings, which you can review through File > Settings.

Each qualifying edit replaces the previous timestamp. If a user changes A2 three times, B2 shows the time of the third edit, not three separate records. When several cells in column A are pasted at once, the script assigns a timestamp to each nonblank row in that edit. Those timestamps may be identical or very close together, which is expected for one paste operation.

Adjust the watched and timestamp columns

The example assumes the editable field is in A and the timestamp belongs in B. If your data starts in another column, change the column numbers consistently: the checks for the watched column, the range read from the sheet, and the range written to must all match your layout. For instance, to watch column C and write timestamps in D, the script needs to test for column 3, read column 3, and write to column 4. Do not simply change one number and assume the rest follows automatically.

If the sheet has multiple tabs, you may also want the script to run on only one of them. Add a sheet-name check near the start of the function, using the name of your tab. This helps avoid timestamps appearing on unrelated tabs that happen to use the same columns. Make sure the spelling and capitalization match the tab name.

Some workflows need a timestamp only the first time a row is filled, so later edits do not replace it. That requires a different rule: check whether the timestamp cell is already populated and only write a date when it is blank. Decide whether you want “last updated” or “first entered” before adapting the script; they answer different questions.

Why NOW() is usually not the right solution

Google Sheets has a NOW function, but it returns the current date and time when the spreadsheet recalculates. It does not permanently capture the moment another cell was edited. A displayed NOW value can change later, making it unsuitable as a reliable last-edited timestamp on its own.

With a timestamp in C2 and a value being tracked in A2, the formula =NOW() in C2 displays the current time, but it is not linked to a specific edit of A2. A formula such as =IF(A2<>"",NOW(),"") also recalculates and can continue showing a newer time. More elaborate formulas that refer back to their own cells rely on iterative calculation and can be fragile when settings or sheet structure change. Use Apps Script for a fixed edit time; use NOW when you actually want a live current-time value.

Limits and common problems

The timestamp does not appear: Confirm the script was saved, that you edited a cell in the watched column below the header, and that the timestamp column is not protected against edits. Check that the code’s column numbers fit your sheet.
A formula change is not detected: Simple onEdit runs for a user’s edit, not whenever a formula result changes because its inputs or a recalculation changed. If you need to record formula-driven changes, you need a different design, such as a time-driven process, and should consider how often it should run.
Changes made by another script or an API do not trigger it: Programmatic edits generally do not invoke a simple onEdit trigger. If an automation updates the sheet, have that automation also write the timestamp, or use an appropriate installable trigger where its event behavior fits the task.
The timestamp column gets overwritten: The example writes to column B. Move the destination or reserve that column so it does not contain user-entered data.
The time looks wrong: Check the spreadsheet time zone under File > Settings and the cell’s number format. A different display format can make the same stored date appear confusing, and a time-zone mismatch can shift the displayed hour.

Simple triggers have execution restrictions and are intended for lightweight tasks. This example only reads and writes cells in the edited spreadsheet. If you expand it to send email, access other files, or use services that require authorization, the simple trigger may not be sufficient; an installable trigger and additional permissions may be needed. Avoid adding unnecessary permissions to a basic timestamp feature.

Timestamp cells, edit history, and audit logs

A timestamp column is convenient because the latest time is visible beside the data, but it preserves only one time per row. Google Sheets version history is better when you need to inspect earlier versions of the spreadsheet. Open File > Version history > See version history to review saved versions. Version history is not the same as a purpose-built, row-by-row audit table, and its presentation may not suit routine reporting.

For a formal change log, a script can append a row to a separate log sheet whenever watched data changes. A useful log might include the edited cell, the new value, the time, and the editor where the available event information supports it. A log needs careful design: pasted ranges, deleted values, permissions, and retention all affect what it captures. Do not assume a timestamp beside a cell proves who made the change or provides a tamper-proof record.

Choosing the right approach

Use Apps Script when a cell should retain a fixed “last changed” time after later recalculations.
Use NOW() when the goal is to show the current date and time, not preserve an edit event.
Use version history when you need to review prior spreadsheet states without building a logging system.
Use a dedicated log when you need a sequence of changes rather than one latest timestamp, and validate that it captures the events your workflow requires.

In Excel, the principle is similar, but Google Apps Script and its onEdit trigger do not run there. Excel users generally use VBA or Office Scripts for automation, depending on the Excel version and environment. A Google Sheets formula or Apps Script should not be pasted into an Excel workbook as though it were an Excel feature.

Frequently Asked Questions

Can Google Sheets add a timestamp automatically when I edit a cell?
Yes. A bound Apps Script with an onEdit trigger can write a fixed timestamp when a user edits a watched cell. The timestamp can go in the next column or another location you choose.

Can I timestamp changes in one column only?
Yes. Check the edited range’s column in the script and write to a dedicated timestamp column. The example watches column A and places dates in B; adjust the column references if your layout differs.

Will the timestamp update when a formula result changes?
Not with the simple onEdit trigger shown here. It responds to user edits, not formula recalculation. A separate automation may be needed if formula-driven changes must be recorded.

Can I keep the original timestamp instead of replacing it?
Yes. Change the script’s rule so it writes a date only when the timestamp cell is blank. That records the first entry time rather than the latest edit time.

Can I see every timestamp for every edit?
Not in one timestamp cell; each new edit overwrites the old value. Use version history or build a separate append-only log if you need a sequence of changes, and verify that it meets your record-keeping needs.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.