Status change stamp
onEdit writes timestamp in column E when column D changes from Open to Closed.
What is a timestamp in Google Sheets?
A timestamp records when something happened: form submitted, status changed, row edited. Use NOW or TODAY for live clock cells, keyboard shortcut Ctrl+Shift+; for static entry time, or onEdit Apps Script to stamp when a checkbox flips. Format cells as date time so serial numbers display human-readably.
When to use it
Use timestamps on approval logs, inventory movements, support ticket updates, and audit columns that prove when data last changed.
When to skip it
Skip volatile NOW in every row of huge sheets. One clock cell or script-based stamp on change events scales better.
How it works
-
1
Format column as Format > Number > Date time before typing or formula results.
-
2
=NOW() updates on recalc; paste values if you need frozen moment of export.
-
3
onEdit script sets adjacent column to new Date() when status column changes to Done.
-
4
Form responses include automatic timestamp column from Google Forms without extra formula.
-
5
Set spreadsheet time zone under File > Settings so NOW aligns with team expectations.
-
6
Combine with version history for who changed what when script stamps are not enough.
Examples in Google Sheets
Manual audit entry
Editor uses Ctrl+Shift+; to stamp review time in QA column without scripting.
Elapsed hours
=(NOW()-A2)*24 shows hours since Created if A2 holds true datetime value.
Build this without starting from a blank cell
Use a Better Sheets tool for Timestamp, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- NOW in ten thousand rows slowing shared file recalc.
- Text timestamps that sort alphabetically not chronologically.
- Time zone surprise when remote team compares NOW without documented sheet zone.
- Overwriting timestamp column with imports that clear audit history.
- Mixing date-only and datetime in one column breaking duration math.
Frequently asked questions
- NOW vs TODAY?
- NOW includes time. TODAY is midnight today as date serial.
- Static timestamp shortcut?
- Ctrl+Shift+; inserts current datetime as value that does not tick forward.
- Timestamp time zone?
- File > Settings > Time zone controls NOW display context for the file.
- Timestamp on edit only?
- Use onEdit or installable edit trigger script, not volatile NOW in every row.
- Format timestamp?
- Date time number format or custom pattern yyyy-mm-dd hh:mm for logs.
- Timestamp in Forms?
- Automatic Timestamp column on linked response sheet.
- Convert text to timestamp?
- DATEVALUE plus TIMEVALUE or VALUE after cleaning separators per locale.
- Timestamp and IMPORTRANGE?
- Imported values are static from source unless source uses NOW and refreshes.
Related Tutorials
Watch how Timestamp works
Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?
Create a Stop Watch in a Google Sheet
Combine Sheets Into One Sheet As It Happens
Spreadsheet Automation 101 Lesson 2: onEdit() Trigger
Track Every Edit (Almost)
Related blog posts
Guides that explain Timestamp in more depth.
How To Create a Stop Watch in Google Sheets
Create a Stop Watch in a Google Sheet. Start and stop time stamps, add up duration once you click. This is a sweet Google Sheet to make and you can see it step-by-step.
Read post →Google Sheets Interface Changes
New Date Chips and Integration with Google Workspace makes Google Sheets much more useful, within the ecosystem. It's a great tool within workspace now.
Read post →Learn to Code in Google Sheets, For Programmers | For Advanced Google Sheet Users
If you know how to code, you'll learn in this step-by-step tutorial how to code in Google Sheets.
Read post →Related glossary terms
onEdit trigger
The onEdit trigger runs a script automatically when someone changes a cell in your spreadsheet. It is one of the simplest ways to make Sheets react to user input without clicking a custom menu. You define the function in Apps Script and bind it to the onEdit event for that file.
Read guide →Google Sheets automation
Automation means work happens without repeating the same clicks every day: imports update, emails send, rows move, and dashboards refresh. In Sheets, automation usually stacks built-in features, Apps Script, and sometimes the Sheets API or add-ons. Start with the lightest tool that still solves the job.
Read guide →cell formatting
Cell formatting controls how values display without necessarily changing the stored value underneath. Currency, percent, date, and plain text formats keep reports readable and prevent Sheets from auto-converting IDs into dates. Good formatting is the difference between a sheet leadership trusts and one they argue with.
Read guide →Apps Script triggers
Triggers tell Apps Script when to run a function: on edit, on open, on a schedule, or when a form is submitted. Simple triggers use reserved names like onEdit. Installable triggers are created in the Apps Script Triggers page and can do more, including running as a specific user or on a timer.
Read guide →Done reading about Timestamp?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.