Timestamp Formula in Google Sheets: Options That Work

For a live date-and-time value in Google Sheets, enter =NOW(); for today’s date without a time, use =TODAY(). These formulas update when Sheets recalculates, so if you need a timestamp that stays fixed after an entry, use a keyboard shortcut, an Apps Script, or a carefully configured iterative formula instead.

What “timestamp” means in a spreadsheet

A timestamp usually means a date and time recorded together, such as 11/3/2026 9:15 AM. In Sheets, it is usually a date-time value, not a special timestamp data type. The value can be used in calculations, sorted chronologically, or displayed in different date and time formats.

There are two importantly different kinds of timestamp:

Dynamic: A formula such as NOW returns the current date and time, then changes when the spreadsheet recalculates.
Fixed: A manually inserted or script-written value records a moment and remains unchanged unless someone edits it.

This difference is the key to choosing a method. A live clock in a cell is not the same as a permanent record of when a form was completed or a task was entered.

Use NOW for a live date and time

With the current date and time needed in A1, enter =NOW() in A1. The formula takes no arguments. It returns the current date and time according to the spreadsheet’s settings, and the displayed result depends on the cell’s number format.

NOW is a volatile function: it can update when Sheets recalculates the spreadsheet, rather than only when the formula itself is edited. Recalculation is not a continuously ticking clock, however. Sheets can recalculate on changes and, if configured, on a timed interval. To inspect those options in the browser, use File > Settings > Calculation. Available recalculation choices include recalculation on change and timed recalculation options; the exact options shown can depend on the spreadsheet.

That behavior makes NOW useful for a dashboard that should show a recent current time, or for calculating elapsed time from a changing reference. It is usually a poor choice for a log where each row must retain the moment it was entered. A formula such as NOW in every row can refresh later and make old entries appear newly created.

Use TODAY when you only need the date

With the date needed in A1, enter =TODAY() in A1. TODAY returns the current date with no meaningful time component, so it is suited to due-date comparisons, daily summaries, and reports that do not need the hour or minute.

For example, with a due date in B2 and a current-date check in C2, a formula can compare the two date values. With the due date in B2 and the comparison formula in C2, enter =B2<TODAY(); the result is TRUE when the due date is earlier than today and FALSE otherwise. Format C2 as a checkbox or leave it as TRUE/FALSE if that is useful. This comparison does not itself create a timestamp and will change as the date advances.

NOW and TODAY return numeric date/time values, even though they look like dates on screen. This means arithmetic is possible: subtracting an earlier date-time from a later one gives an elapsed-time value. The result may need a duration format to display as hours and minutes rather than as a calendar date.

Format a timestamp so it is readable

If a cell shows a decimal number or an unexpected date, the underlying value may be fine; its number format may be unsuitable. Select the cell or range and choose Format > Number, then select a date, time, or date-time format. To specify the display more precisely, use Format > Number > Custom date and time and choose or assemble a pattern, such as a date followed by hours and minutes.

Formatting changes how a value appears, not the stored moment or the result of NOW. For example, a format that omits seconds can hide them from view without removing precision from the underlying value. Conversely, a formula using TEXT can turn a date-time into text, which may be less convenient for sorting or arithmetic.

With a timestamp value in A1, enter =TEXT(A1,"yyyy-mm-dd hh:mm:ss") in B1 to display it as text in a consistent year-month-day and 24-hour format. This is helpful for labels or exports, but B1 is text, not a date-time value for normal date calculations. Prefer cell formatting when you want to retain a usable date-time value.

Insert a fixed timestamp without a formula

For a one-time entry, a keyboard shortcut is often the simplest fixed method. In Google Sheets in a browser on Windows or ChromeOS, Ctrl+; inserts the current date in the selected cell, while Ctrl+Shift+; inserts the current time. On a Mac, the corresponding shortcuts generally use Command in place of Ctrl: Command+; for the date and Command+Shift+; for the time. Keyboard layout, browser, and operating-system settings can affect shortcuts, so check Help > Keyboard shortcuts in Sheets if a key combination does not work.

You can insert the date and time into the same cell by using the date shortcut, adding a space, then inserting the time shortcut, depending on the keyboard and input behavior. Another straightforward option is to insert them in neighboring cells and combine the values if needed. These shortcuts insert values; they do not leave a NOW or TODAY formula behind. The inserted value will not update like a live formula.

For a simple checklist or manually maintained log, this is often more reliable than a formula-based timestamp. It requires a person to perform the shortcut, however, and it does not automatically prove who entered the data or prevent later changes.

Stamp a row with a formula and iterative calculation

A formula can preserve a first-entry time by referring back to its own cell, but this method deliberately creates a circular reference. Suppose column A contains an item or status starting at A2, and column B should hold its first timestamp starting at B2. Put this formula in B2 and fill it down:

With the trigger value in A2 and the timestamp formula in B2, enter =IF(A2<>"",IF(B2="",NOW(),B2),"") in B2.

For this circular-reference method to work, enable iterative calculation using File > Settings > Calculation and turn on iterative calculation. The formula checks whether A2 is nonblank; if B2 has no timestamp yet, it returns NOW, and on subsequent recalculations it refers to the value already in B2. Without iterative calculation, Sheets reports a circular dependency rather than producing the intended result.

This is a workaround, not a robust audit trail. Iteration is a spreadsheet-level calculation setting, the formula is easy to overwrite, and changing or clearing the trigger can produce behavior that is not appropriate for every workflow. Test it on a copy before using it in an important sheet. For a dependable automatic timestamp, a script is usually clearer.

Automatically timestamp new entries with Apps Script

An Apps Script can write a fixed date-time value into a neighboring cell when a person edits a row. In a bound spreadsheet, open Extensions > Apps Script, add a script like the following, and save it. This example watches column A on a tab named Sheet1, starting at row 2, and writes the first timestamp in column B:

function onEdit(e) {
  if (!e || !e.range) return;
  const cell = e.range;
  const sheet = cell.getSheet();
  if (sheet.getName() !== "Sheet1") return;
  if (cell.getNumRows() !== 1 || cell.getNumColumns() !== 1) return;
  if (cell.getColumn() !== 1 || cell.getRow() < 2 || cell.isBlank()) return;

  const stamp = sheet.getRange(cell.getRow(), 2);
  if (stamp.isBlank()) stamp.setValue(new Date());
}

Change Sheet1 if your tab has a different name. This simple onEdit trigger runs when a user directly edits a cell in the spreadsheet. It deliberately ignores multi-cell pastes, leaves an existing timestamp alone, and does not erase the timestamp if the entry in column A is later cleared. It also does not run merely because a formula recalculates, or in response to edits made by another script or an API. For more complicated workflows, the script needs additional handling.

After the script writes a date-time, format column B using Format > Number. Scripts are useful when timestamps must be created automatically, but they require care: restrict editing access as appropriate, test the trigger on sample data, and remember that a timestamp alone is not tamper-proof evidence.

Time zones, recalculation, and common problems

A spreadsheet has a time-zone setting that affects how date-time values are interpreted and displayed. Review it under File > Settings > General. If NOW or a script timestamp appears offset from the time you expect, check the spreadsheet’s time zone and the viewer’s expectations before changing the formula. Collaborators in different regions can see the same stored moment rendered in different local contexts.

The cell shows a number: Apply a date-time number format through Format > Number.
The timestamp keeps changing: The cell likely contains NOW or TODAY. Use a fixed shortcut or script when the original entry time must persist.
The formula returns a circular dependency: The self-referencing row formula requires iterative calculation. If you do not want that setting, use another method.
The time is wrong by hours: Check the spreadsheet time zone under File > Settings > General.
The output is text: A TEXT formula formats a date for display as text. Use number formatting instead if you need to calculate with the result.

One frequent misconception is that a timestamp formula automatically records when a neighboring cell was edited. NOW does not know which row was changed; it simply recalculates as a spreadsheet function. A row-by-row automatic record needs a mechanism that stores a value when an edit occurs, such as Apps Script or the iterative formula workaround.

Choosing the right timestamp method

Use NOW when the intended meaning is “the current date and time whenever the sheet recalculates.” Use TODAY for a date-only value. Choose the keyboard shortcut when a person can insert a fixed value at the moment of entry. For a first-entry timestamp triggered by a direct edit, use Apps Script where permitted; use the circular formula only when its limitations and iterative setting are acceptable.

Excel differs in its formula environment and menu paths, so the instructions above are specifically for Google Sheets in a web browser. In both programs, a volatile current-time formula and a permanent entry-time record are distinct concepts; copying an Excel timestamp formula into Sheets does not make it fixed. For records with compliance, access-control, or audit requirements, use a system designed for those requirements rather than treating an editable spreadsheet timestamp as secure proof.

Frequently Asked Questions

What is the timestamp formula in Google Sheets?
Use =NOW() for the current date and time, or =TODAY() for today’s date. Both recalculate; neither automatically creates an unchanging record of when another cell was edited.

How do I keep a Google Sheets timestamp from changing?
Insert a fixed date or time with the relevant keyboard shortcut, or write a value using Apps Script. A self-referencing formula can also hold a first value when iterative calculation is enabled, but it is more fragile.

Can Google Sheets add a timestamp when I enter data in a row?
Yes. An Apps Script onEdit trigger can write a fixed timestamp into a neighboring cell when a user edits a chosen column. A formula-only workaround is possible with iterative calculation, but it is not a secure audit mechanism.

Why does NOW not update every second?
NOW updates when Sheets recalculates the spreadsheet, not as a guaranteed continuously running clock. Recalculation settings can allow timed updates, but the function is not intended as a precision live clock.

How do I show seconds in a timestamp?
Select the timestamp cells and use Format > Number > Custom date and time, then select a pattern that includes seconds. This changes the display, not the underlying timestamp value.

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.