For a live current date in Excel, use =TODAY(); for the current date and time, use =NOW(). Neither formula records when a workbook, row, or cell was last edited: a true last-updated timestamp needs a different method, such as a VBA event macro.
What “last updated date” can mean in Excel
The phrase can refer to several different things, and the right solution depends on which one you need. You might want today’s date displayed on a worksheet, a date that appears when someone fills in a row, a timestamp that changes whenever a row is edited, or the date the workbook file was last saved. Those are different tasks.
Excel’s ordinary worksheet formulas calculate values from data and the current calculation environment. They do not provide a built-in formula that reliably asks, “When did someone last edit this cell?” A formula such as =NOW() can show the current time when recalculated, but it cannot independently preserve the time of an earlier edit. If you need an auditable history of changes, a single timestamp cell is also not enough; use a version history or change-tracking system suitable for your organization.
- Show today’s date: use
=TODAY(). - Show the current date and time: use
=NOW(). - Stamp a row when a user edits it: use VBA or another controlled automation method.
- Find when the file was saved: check file properties or use a suitable automation workflow; there is no standard worksheet formula for this.
How TODAY and NOW work
With no source cells involved and the goal of displaying the current date in an output cell, enter =TODAY(). It returns the current date as an Excel date value. The value can change when Excel recalculates the workbook on a later day, so it is appropriate for labels such as “Today” or a daily report date—not for a permanent record of when an action happened.
With no source cells involved and the goal of displaying the current date and time, enter =NOW(). It returns a date-and-time value, usually displayed with a time only if the cell’s number format includes one. NOW is a volatile function: it can recalculate when Excel recalculates, even if the formula’s cell was not edited. It therefore shows a recalculation time, not necessarily the last edit time.
In Microsoft 365 for Windows, calculation is normally set to automatic. You can check it at Formulas > Calculation Options > Automatic. Recalculation can also occur when you open a workbook or make other changes, so TODAY and NOW are not fixed stamps. Changing calculation mode affects workbook behavior more broadly; it does not turn either function into a true edit-history function.
Display a date when a row has data
Sometimes a user wants a date beside a completed row, rather than a live date in a report heading. A formula can conditionally display today’s date while a row contains data, but the result will still move forward as TODAY recalculates. It is a useful status display, not a permanent timestamp.
Assume column A contains a task name and column B is the date display. In B2, enter =IF(A2<>"",TODAY(),""), then fill the formula down. If A2 is blank, B2 stays blank; if A2 contains text, B2 displays the current date. When the sheet recalculates on a later day, the displayed date can update. The formula does not know when A2 was first filled.
If you want to apply this, use it for a dashboard or a list where “date shown while this row is active” is the intended meaning. Do not label it “created on” or rely on it as evidence of when a task was entered, because the formula’s result is not a stored event record.
Why a formula alone cannot preserve an edit timestamp
A formula’s result is recalculated from its inputs; it does not normally write a value back into another cell and freeze it. That distinction explains a common misconception: placing NOW inside an IF statement does not automatically make a permanent timestamp. If the formula recalculates, NOW can return a newer time.
There is a circular-reference workaround, but it depends on iterative calculation and needs careful setup. Assume B2 is the timestamp cell and C2 is the input cell that indicates the row is in use. In B2, enter =IF(C2<>"",IF(B2="",NOW(),B2),""). Because the formula refers to B2 itself, it is circular. With iterative calculation enabled, it can retain the first timestamp while C2 remains nonblank. It does not automatically update for every later edit to the row, and clearing C2 clears the displayed stamp. Do not use this workaround without testing a copy of the workbook and understanding the calculation settings. It is not a general-purpose last-edited solution.
Use VBA for a timestamp that changes on edits
For a worksheet that needs a stored timestamp whenever someone edits designated cells, a VBA worksheet-change event is a more direct option. The following example watches A2:D100 and writes the current date and time to column E on each affected row. It updates the stamp when any watched cell is changed, including when a user pastes values into multiple watched cells.
Right-click the relevant sheet tab and choose View Code. Paste the code into that worksheet’s code window, not a standard module. Save the file as an Excel Macro-Enabled Workbook (.xlsm) and ensure macros are permitted under your organization’s security rules.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim changed As Range
Dim cell As Range
On Error GoTo CleanUp
Set changed = Intersect(Target, Me.Range("A2:D100"))
If changed Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cell In changed.Cells
Me.Cells(cell.Row, "E").Value = Now
Next cell
CleanUp:
Application.EnableEvents = True
End Sub
The code writes a value rather than a formula, so the timestamp does not advance just because Excel recalculates. Format column E to show the detail you need: for example, a date-and-time number format if you need both. This sample records the most recent edit to the watched range on each row, not a full audit trail. It also responds to direct edits, not changes that occur only because a formula recalculates.
Before running or adapting any macro, save a separate copy of the workbook. Macro actions cannot be undone with Ctrl+Z, and an error in event code can affect the workbook’s behavior. If the macro must cover more rows or columns, change the watched range deliberately and test pasting, deleting, and editing multiple cells.
Check the workbook’s file update time
If you mean the time the workbook file was last modified or saved, a worksheet formula is usually the wrong place to look. In Windows File Explorer, right-click the file and choose Properties; the Modified value gives the file-system modification time. That tells you about the file, not which worksheet or cell changed, and a copy, download, or synchronization service can affect the date shown.
Excel’s document information and cloud version history may offer useful context, depending on where the file is stored and how it is managed. For a workbook in OneDrive or SharePoint, version history can help show earlier saved versions and who made changes when account and sharing details are available. It is more informative than a formula timestamp for reviewing file versions, but it is not the same as a cell-by-cell audit log.
Format and interpret the result correctly
Excel stores dates as serial values, so the formula result and the way it appears are separate. If a cell containing TODAY displays a number, select it and choose a date format from Home > Number. For NOW, choose a format that displays both date and time. You can also open the Format Cells dialog to select a built-in date or time format, or define a custom display.
Formatting does not change the underlying value. A date-only format can hide the time portion of NOW, even though the time is still present. Conversely, a time-only format can hide the date. If comparing timestamps, make sure the cells use compatible values and formats; display alone does not tell you whether a formula recalculated or a timestamp was stored as a fixed value.
Choose the method that matches your need
Choose the simplest method that represents what you actually need. A live report heading needs a formula; a row-level edit stamp needs an event-based process; a saved-file date belongs to file properties or version history. Avoid describing a changing formula result as a permanent “last updated” record.
- Use TODAY when the worksheet should always show the current date.
- Use NOW when the worksheet should show the date and time at recalculation.
- Use a VBA event macro when a cell should store the time of a direct edit to a defined range.
- Use cloud version history or an approved audit process when you need to review previous saved versions or changes.
For shared workbooks, consider who can run macros and whether every editor will use desktop Excel with macros enabled. If that cannot be guaranteed, the macro may not stamp every edit. A process that depends on one person’s local Excel settings is not a dependable team-wide record.
Frequently Asked Questions
What formula inserts today’s date in Excel?
With no source cells involved, enter =TODAY() in the cell where you want the date. It updates when the workbook recalculates, so it is not a fixed entry date.
How do I show the current date and time?
With no source cells involved, enter =NOW(). Format the cell to show both date and time. The result can change on recalculation.
Can Excel automatically update a cell when another cell changes?
A regular formula does not reliably preserve the time of an edit. A VBA Worksheet_Change event can write a fixed timestamp when a user changes a specified range, provided macros are enabled.
Can TODAY or NOW be used as a permanent timestamp?
No. They are recalculating functions. Their results can change when Excel recalculates, so use a stored value written by an appropriate macro or workflow for a fixed timestamp.
How can I see when an Excel file was last saved?
Check the file’s Modified time in Windows File Explorer, or review version history if the workbook is stored in a service such as OneDrive or SharePoint. This reports file-level information, not a specific cell’s edit time.
Related
- Excel Conditional Formatting Icon Sets Explained
- Markdown Table Merge Cells? What You Can Do
- PHP Backend Data Validation: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- EDATE in Excel: Add Months to a Date, With ExamplesTo add calendar months to a date in Excel, use =EDATE(start_date, months). For example, with a date in…
- Combine Cells in Excel That Have Words: Formulas and StepsTo combine words from two Excel cells, enter a formula such as =A2&" "&B2 in an empty cell;…
- Excel Data Validation INDIRECT for Dependent ListsYes. In Excel, INDIRECT can make a data-validation drop-down depend on another cell: name each child list after…
- NPV Calculator in Excel: Formula, Example, and TipsTo calculate net present value (NPV) in Excel, enter the discount rate and future cash flows, then use…
- How to Sort Excel Alphabetically by First ColumnTo sort an Excel list alphabetically by its first column, click a cell in that column and choose…
- Excel Sort Multiple Columns: Rules, Steps, and TipsTo sort Excel data by multiple columns, select a cell in the data, choose Data > Sort, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.