Excel timestamps are useful when you need to record when data was entered, updated, reviewed, or completed. With VBA, you can create date and time stamps that are either inserted manually with a macro or added automatically when a user changes a cell.
Automatic timestamps are especially helpful for tracking activity in logs, task lists, inventory sheets, request forms, and shared workbooks. By using worksheet events such as cell changes, VBA can monitor specific columns or ranges and place a timestamp in the correct location without requiring users to type it themselves.
As an Amazon Associate I earn from qualifying purchases.
A good timestamp macro should also handle formatting, avoid overwriting existing values when needed, and be saved in a macro-enabled workbook so it runs reliably. With a few precautions, VBA can make Excel timestamp tracking consistent, accurate, and easy to maintain.
How VBA Date and Time Stamps Work in Excel
In Excel VBA, a date and time stamp is usually created by writing the current system date, current system time, or both into a worksheet cell. VBA gets these values from your computer clock using built-in functions such as Date, Time, and Now. The Date function returns only the current date, Time returns only the current time, and Now returns both the current date and time as one value.
#1 Best Overall
- DATE STAMP - ExcelMark's new date stamp offers a better quality stamp than most daters. With 10 years worth of dates, this stamp is expected to last for a long time. To change the date, simply press the base down and turn the black dials to keep your fingers mess free!
- NEW BAND SHIELD: This new feature protects your fingers from getting covered in ink when changing the dates on your stamp.
- CLEAN & EASY – The ergonomic shape of this self-inking stamp allows you to maximize workflow while saving your wrist from any discomfort. The open-base design allows for better alignment before any ink touches the paper. The self-contained ink pad and re-inking process are ideal for repetitive stamping.
- CLEAR & CRISP IMPRESSION – With an impression area of 4mm x 20mm, our date stamp provides a legible and crisp impression of the date specified, allowing you to stay organized, stress-free, and confident in every stamp.
- VERSATILE USAGE – This stamp is great for office staff, teachers, accountants, warehouse clerks, librarians, and small businesses to stamp dozens of pages in one sitting efficiently. Our date stamps can be used for shipping, receiving, expiration, and due dates.
For most timestamp workflows, Now is the most practical choice because it records the exact moment an action happens. For example, if a user enters a task status in column B, a VBA procedure can place Now in column C on the same row. This creates a fixed record such as 3/15/2026 10:42 AM, depending on the workbook’s formatting. Although the value appears as a readable date and time, Excel stores it internally as a serial number, which means it can be sorted, filtered, compared, or used in formulas.
Static timestamps versus automatic timestamps
VBA timestamps generally fall into two categories: manual static stamps and event-based automatic stamps. A manual stamp is created when the user runs a macro, clicks a button, or uses a shortcut. The macro writes the current date and time into the selected cell or into a defined target cell. Once written, the value stays the same unless another macro, formula, or user action changes it.
An event-based timestamp is created when Excel detects a specific worksheet action. The most common method is the Worksheet_Change event, which runs automatically when a user edits a cell. This is useful for tracking entries, updates, approvals, inventory changes, or status changes without requiring users to remember to run a macro manually.
Free tools Windows power users keep installed
One-click scans. No signup required.
| VBA function or event | What it does | Common use |
|---|---|---|
| Date | Returns the current date only | Recording the day an item was entered |
| Time | Returns the current time only | Logging the time of an action during the day |
| Now | Returns the current date and time | Creating a complete timestamp |
| Worksheet_Change | Runs when a worksheet cell is changed | Adding timestamps automatically after data entry |
A major difference between VBA timestamps and worksheet formulas is that VBA can write a fixed value. A formula such as =NOW() recalculates whenever Excel recalculates the workbook, so it does not reliably preserve the original entry time. VBA, by contrast, can place the current value of Now into a cell as a constant. This makes it better suited for audit trails, logs, and update tracking.
Because event-based macros respond to worksheet edits, they need clear boundaries. A well-designed timestamp macro should only react to the intended cells, such as column A for new entries or columns B:D for updates. It should also avoid repeatedly changing the timestamp when unrelated cells are edited. Many timestamp procedures include checks for the edited range, blank cells, protected timestamp columns, and existing values so that the macro records changes accurately without creating unwanted overwrites or triggering repeated events.
Insert a Static Date and Time Stamp with a Macro
A static date and time stamp is a fixed value inserted into a cell at the moment the macro runs. Unlike formulas such as =NOW() or =TODAY(), the value will not recalculate when the worksheet changes or when the workbook is opened later. This is useful for recording when an entry was created, when a task was completed, or when a report was generated.
The simplest approach is to use VBA’s Now function, which returns the current system date and time. You can place that value into the active cell, a specific cell, or a cell next to the selected data. To create the macro, press Alt + F11 to open the Visual Basic Editor, choose Insert > Module, and add a procedure such as this:
Recommended Free Tools
Sub InsertDateTimeStamp()
ActiveCell.Value = Now
ActiveCell.NumberFormat = "m/d/yyyy h:mm AM/PM"
End Sub
When you run this macro, Excel writes the current date and time into the selected cell and applies a readable format. Because the macro assigns a value directly to the cell, the timestamp remains unchanged unless someone edits the cell or runs another macro that replaces it. You can run the macro from the Macros dialog box with Alt + F8, assign it to a button, or add it to the Quick Access Toolbar for faster use.
Insert the timestamp into a fixed cell
If the timestamp should always go into the same location, refer to that cell directly instead of using ActiveCell. This works well for dashboards, printed reports, or templates where a “Last generated” field appears in a fixed position.
Sub StampReportTime()
With Worksheets("Report").Range("B2")
.Value = Now
.NumberFormat = "dddd, mmmm d, yyyy h:mm AM/PM"
End With
End Sub
This example writes the timestamp to cell B2 on the worksheet named Report. The With block keeps the code tidy and ensures that both the value and format are applied to the same cell. If your worksheet has a different name, update "Report" to match it exactly.
Insert only the date or only the time
For records that need only a calendar date, use the Date function. For records that need only the time of day, use the Time function. Both create static values when assigned to a cell.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Sub InsertDateOnly()
ActiveCell.Value = Date
ActiveCell.NumberFormat = "m/d/yyyy"
End Sub
Sub InsertTimeOnly()
ActiveCell.Value = Time
ActiveCell.NumberFormat = "h:mm:ss AM/PM"
End Sub
Use a static macro when the user should decide exactly when the stamp is created. For example, a team member may select a row and click a “Mark Complete” button to record completion time. If the timestamp must appear automatically after data is entered, an event-based worksheet macro is better suited, since it can respond immediately when a cell changes.
Automatically Add a Timestamp When a Cell Changes
You can make Excel add a timestamp automatically by using the Worksheet_Change event. This event runs whenever a user edits a cell on a specific worksheet. A common setup is to let users enter data in one column and have VBA place the current date and time in another column on the same row.
For example, suppose column A contains task names or order numbers, and column B should record when each entry was added or changed. Right-click the worksheet tab, choose View Code, and place this macro in the worksheet code window, not in a standard module:
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsPrivate Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now
Target.Offset(0, 1).NumberFormat = "m/d/yyyy h:mm AM/PM"
Application.EnableEvents = True
End Sub
In this example, editing a cell in column A writes the timestamp into the cell one column to the right. If you type in A2, Excel records the date and time in B2. The Now function returns both the current date and current time, while NumberFormat controls how the value appears in the worksheet.
The Intersect line limits the macro to changes in column A. Without that check, the event would run after any worksheet edit, including edits to the timestamp column itself. The Application.EnableEvents = False line is also essential because the macro changes a cell, and that change could otherwise trigger the same event again. Events are turned back on after the timestamp is written.
Timestamp only when a new value is entered
If you want the timestamp to appear only when a cell receives a value, and to clear when the source cell is emptied, you can test the edited cell before writing the timestamp:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
If Target.CountLarge > 1 Then Exit Sub
Rank #2
- DATE STAMP - ExcelMark's new date stamp offers a better quality stamp than most daters. With 10 years worth of dates, this stamp is expected to last for a long time. To change the date, simply press the base down and turn the black dials to keep your fingers mess free!
- NEW BAND SHIELD: This new feature protects your fingers from getting covered in ink when changing the dates on your stamp.
- CLEAN & EASY – The ergonomic shape of this self-inking stamp allows you to maximize workflow while saving your wrist from any discomfort. The open-base design allows for better alignment before any ink touches the paper. The self-contained ink pad and re-inking process are ideal for repetitive stamping.
- CLEAR & CRISP IMPRESSION – With an impression area of 4mm x 20mm, our date stamp provides a legible and crisp impression of the date specified, allowing you to stay organized, stress-free, and confident in every stamp.
- VERSATILE USAGE – This stamp is great for office staff, teachers, accountants, warehouse clerks, librarians, and small businesses to stamp dozens of pages in one sitting efficiently. Our date stamps can be used for shipping, receiving, expiration, and due dates.
Application.EnableEvents = False
If Target.Value <> "" Then
Target.Offset(0, 1).Value = Now
Target.Offset(0, 1).NumberFormat = "m/d/yyyy h:mm AM/PM"
Else
Target.Offset(0, 1).ClearContents
End If
Application.EnableEvents = True
End Sub
The Target.CountLarge check prevents the macro from trying to process a large pasted range as if it were a single cell. This is useful when users paste mulle rows of data, because a basic event macro that relies on one edited cell can behave unpredictably with multi-cell changes.
Handle pasted data across multiple rows
If users may paste several values into column A at once, loop through each changed cell and timestamp the matching row:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim changedCells As Range
Set changedCells = Intersect(Target, Me.Range("A:A"))
If changedCells Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cell In changedCells
If cell.Value <> "" Then
cell.Offset(0, 1).Value = Now
cell.Offset(0, 1).NumberFormat = "m/d/yyyy h:mm AM/PM"
Else
cell.Offset(0, 1).ClearContents
End If
Next cell
Application.EnableEvents = True
End Sub
This version is safer for everyday worksheets because it supports both individual edits and pasted lists. If the source cell is filled, the adjacent timestamp is added or refreshed. If the source cell is cleared, the matching timestamp is removed, keeping the row consistent.
Apply Timestamps to Specific Columns or Ranges
After you have a worksheet event macro working, the next step is usually to limit it to the cells that should trigger a timestamp. In many worksheets, you do not want every edit to create a date and time value. For example, a task tracker may need a timestamp only when someone updates the status in column C, while an inventory sheet may need a timestamp only when quantities in column E change.
The most common approach is to use Intersect inside the Worksheet_Change event. This checks whether the edited cell overlaps with a specific column, row, or range. If it does not, the macro exits without changing anything. The following example adds a timestamp in column D whenever a cell in column C is edited:
Private Sub Worksheet_Change(ByVal Target As Range)
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallIf Intersect(Target, Me.Range("C:C")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now
Target.Offset(0, 1).NumberFormat = "m/d/yyyy h:mm AM/PM"
Application.EnableEvents = True
End Sub
In this example, Target is the changed cell, and Target.Offset(0, 1) means “same row, one column to the right.” If the user changes cell C5, Excel writes the timestamp to D5. This layout works well for status logs, approval sheets, customer follow-up lists, and any table where the timestamp sits beside the edited value.
Limit the timestamp to a smaller range
You can make the trigger more precise by replacing an entire column with a defined range. For instance, the next macro only responds to changes in C2:C100. This avoids timestamps being added when a header cell or cells outside the active data area are edited:
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("C2:C100")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now
Target.Offset(0, 1).NumberFormat = "yyyy-mm-dd hh:mm"
Application.EnableEvents = True
End Sub
You can also target mulle columns or non-adjacent ranges. For example, if updates in columns B and E should both create timestamps, use a combined range. In the sample below, changes in column B write a timestamp to column C, while changes in column E write a timestamp to column F:
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B:B,E:E")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
Target.Offset(0, 1).Value = Now
Target.Offset(0, 1).NumberFormat = "m/d/yyyy h:mm AM/PM"
End If
Rank #3
- DATE STAMP - ExcelMark's new date stamp offers a better quality stamp than most daters. With 10 years worth of dates, this stamp is expected to last for a long time. To change the date, simply press the base down and turn the black dials to keep your fingers mess free!
- NEW BAND SHIELD: This new feature protects your fingers from getting covered in ink when changing the dates on your stamp.
- COMPLETE COLOR CUSTOMIZATION - Our date stamp allows you to choose both its ink and mount colors. There are 5 options for the mount: Violet, Peach, Pink, Seafoam, and Gray, as well as 3 options for ink color: Black, Red, or Blue. Select the color combination that works best for you!
- CLEAN & EASY – The ergonomic shape of this self-inking stamp allows you to maximize workflow while saving your wrist from any discomfort. The open-base design allows for better alignment before any ink touches the paper. The self-contained ink pad and re-inking process are ideal for repetitive stamping.
- CLEAR & CRISP IMPRESSION – With an impression area of 4mm x 20mm, our date stamp provides a legible and crisp impression of the date specified, allowing you to stay organized, stress-free, and confident in every stamp.
If Not Intersect(Target, Me.Range("E:E")) Is Nothing Then
Target.Offset(0, 1).Value = Now
Target.Offset(0, 1).NumberFormat = "m/d/yyyy h:mm AM/PM"
End If
Application.EnableEvents = True
End Sub
Handle pasted data carefully
When users paste several cells at once, Target may contain mulle cells instead of one. A safer version loops through each changed cell and adds a timestamp to the matching row. This is useful when copying several task statuses, product counts, or review outcomes into the sheet at the same time:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ChangedCell As Range
Dim WatchRange As Range
Set WatchRange = Me.Range("C2:C100")
If Intersect(Target, WatchRange) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each ChangedCell In Intersect(Target, WatchRange)
ChangedCell.Offset(0, 1).Value = Now
ChangedCell.Offset(0, 1).NumberFormat = "m/d/yyyy h:mm AM/PM"
Next ChangedCell
Application.EnableEvents = True
End Sub
For structured Excel tables, you can still use the same method, but fixed ranges such as C2:C100 may need adjustment as the table grows. If your data expands often, consider using a larger range, a named range, or a table column reference in your VBA. Keeping the watched range narrow helps prevent accidental timestamps and makes the macro easier to maintain.
Format Date and Time Values in VBA
After VBA writes a timestamp to a worksheet, you can control how that value appears by applying a number format to the target cell. In most cases, the macro should store a real Excel date and time value, such as the result of Now, and then format the cell separately. This keeps the timestamp usable for sorting, filtering, calculations, pivot tables, and elapsed-time formulas.
For example, if a timestamp is written to column B when column A changes, the macro can place the current date and time in the adjacent cell and apply a readable format at the same time:
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
Application.EnableEvents = True
End Sub
The .Value = Now line stores the full date and time, while .NumberFormat controls the display. You can adjust the format string to match the way your workbook is used. A short date may be enough for task tracking, while a seconds-level timestamp may be needed for logs, order processing, or audit-style worksheets.
| VBA number format | Example display | Common use |
|---|---|---|
| mm/dd/yyyy | 03/18/2026 | Date only |
| dd-mmm-yyyy | 18-Mar-2026 | Readable reports |
| hh:mm AM/PM | 02:45 PM | Time only |
| mm/dd/yyyy hh:mm | 03/18/2026 14:45 | General timestamps |
| yyyy-mm-dd hh:mm:ss | 2026-03-18 14:45:09 | Logs and sortable records |
You can also use Date or Time instead of Now when you do not need both parts of the timestamp. Date stores only the current date, and Time stores only the current time. For example, a daily checklist might use .Value = Date with .NumberFormat = “dd-mmm-yyyy”, while a sign-in sheet might use .Value = Time with .NumberFormat = “hh:mm:ss AM/PM”.
Free tools Windows power users keep installed
One-click scans. No signup required.
Be careful not to confuse NumberFormat with the VBA Format function. If you use .Value = Format(Now, “mm/dd/yyyy hh:mm:ss”), Excel may store the result as text instead of a true date-time number. That can make later calculations harder. A better pattern is to assign Now, Date, or Time directly to the cell, then use NumberFormat to control appearance.
If your workbook is shared across regions, consider using an unambiguous format such as yyyy-mm-dd hh:mm:ss. Formats like 03/04/2026 can be read as March 4 or April 3 depending on local settings. A consistent timestamp format helps prevent confusion when data is exported, reviewed by other teams, or imported into another system.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Prevent Timestamps from Being Overwritten
When a timestamp is meant to record the first time a row was updated, the VBA code should check whether the timestamp cell already contains a value before writing to it. Without this check, every edit to the watched cell or row can replace the original date and time, making the timestamp less useful for audit trails, submission logs, or task tracking sheets.
A common pattern is to place the timestamp in a nearby column only if that cell is blank. For example, if users enter data in column A and the timestamp belongs in column B, the worksheet event can test column B before assigning Now. This preserves the original timestamp after the first entry:
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsPrivate Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
If Me.Cells(Target.Row, "B").Value = "" Then
Me.Cells(Target.Row, "B").Value = Now
Me.Cells(Target.Row, "B").NumberFormat = "m/d/yyyy h:mm AM/PM"
End If
Application.EnableEvents = True
End Sub
The blank-cell test is the part that prevents overwriting. If column B already has a timestamp, the macro leaves it unchanged even when the value in column A is edited later. This approach works well for “created on” dates. If you also need a “last modified” timestamp, use a separate column, such as column C, and allow that column to update on every change while column B remains locked to the first entry.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
If Target.CountLarge > 1 Then Exit Sub
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Application.EnableEvents = False
If Me.Cells(Target.Row, "B").Value = "" Then
Me.Cells(Target.Row, "B").Value = Now
Me.Cells(Target.Row, "B").NumberFormat = "m/d/yyyy h:mm AM/PM"
End If
Rank #4
- DATE STAMP - ExcelMark's new date stamp offers a better quality stamp than most daters. With 10 years worth of dates, this stamp is expected to last for a long time.
- COMPLETE CUSTOMIZATION - Our date stamp with text allows you to choose from 6 static phrases and 5 mount colors. We even allow you to enter custom text for more unique and personal uses.
- CLEAN & EASY - The scroll wheels on our date stamps allow you to effortlessly change the date of your stamp without getting any ink on your fingers.
- CLEAR & CRISP IMPRESSION - With an impression area of 0.9"x 1.7", our date stamp provides a legible and crisp impression of the date specified.
- VERSATILE USAGE - Our date stamps can be used for shipping, receiving, expiration, and due dates. Our stamps are best used on paper, parchment, cardboard, and plastic surfaces.
Me.Cells(Target.Row, "C").Value = Now
Me.Cells(Target.Row, "C").NumberFormat = "m/d/yyyy h:mm AM/PM"
Application.EnableEvents = True
End Sub
It is also useful to protect timestamp cells from manual edits. You can format the timestamp column as locked, unlock the input columns, and then protect the worksheet. In Excel, select the cells users may edit, open Format Cells, clear Locked, and protect the sheet from the Review tab. If your macro needs to write to protected cells, protect the sheet with UserInterfaceOnly:=True when the workbook opens, so VBA can update timestamps while users cannot edit them directly.
For more reliable code, make sure events are turned back on even if an error occurs. If a macro stops while Application.EnableEvents is set to False, worksheet change events will not run again until events are re-enabled. A safer version uses an error handler:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo CleanExit
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
If Me.Cells(Target.Row, "B").Value = "" Then
Me.Cells(Target.Row, "B").Value = Now
Me.Cells(Target.Row, "B").NumberFormat = "m/d/yyyy h:mm AM/PM"
End If
CleanExit:
Application.EnableEvents = True
End Sub
Before using this in a shared workbook, decide whether timestamps should record the first entry, the most recent edit, or both. Keep those purposes in separate columns, avoid clearing timestamp cells in bulk operations, and test the macro on a copy of the workbook before applying it to live data.
Save and Enable the Workbook for VBA Macros
After adding VBA timestamp code, the workbook must be saved in a macro-enabled format. A standard Excel workbook with the .xlsx extension cannot store VBA procedures, including worksheet change events that insert dates and times automatically. If you save a file containing VBA as .xlsx, Excel will remove the macro code and the timestamp feature will stop working.
Use the Excel Macro-Enabled Workbook format instead. In Excel, go to File > Save As, choose a folder, open the file type list, and select Excel Macro-Enabled Workbook (*.xlsm). Give the workbook a clear name, such as Sales_Log_Timestamps.xlsm, so other users can tell that the file contains automation. If you are building a reusable template, use Excel Macro-Enabled Template (*.xltm) instead.
Enable macros when opening the workbook
When the workbook is opened, Excel may block VBA until the user allows it. If a yellow security bar appears below the ribbon, click Enable Content to allow the timestamp macros to run. Event-based macros, such as code inside Worksheet_Change, will not respond to edits while macros are disabled. This means rows can be changed without timestamps being written, so users should enable content before entering or editing tracked data.
- .xlsm: Best choice for regular workbooks that contain VBA timestamp macros.
- .xltm: Best choice for macro-enabled templates used to create new timestamp workbooks.
- .xlsb: Binary workbook format that can store macros and may be useful for very large files.
- .xlsx: Does not support VBA and should not be used for the final macro version.
Use trusted locations for recurring files
If you use the timestamp workbook often, consider storing it in a trusted location configured in Excel. Go to File > Options > Trust Center > Trust Center Settings > Trusted Locations. Files opened from trusted locations can run macros without repeated security prompts, depending on your organization’s settings. This is useful for shared logs, production trackers, attendance sheets, service records, and other files where timestamp automation needs to work every time.
For shared or business workbooks, keep the VBA file in a controlled location and avoid sending edited copies back and forth by email. Mulle versions can lead to missing code, disabled macros, or conflicting timestamp behavior. If the workbook is stored on OneDrive, SharePoint, or a network drive, test the macro after saving and reopening it. Also confirm that users are opening the file in the desktop version of Excel, because VBA macros do not run in Excel for the web.
Before distributing the workbook, close and reopen it, enable macros, and test a few edits in the monitored cells. Check that the timestamp appears in the correct column, uses the intended date and time format, and does not replace existing values unless that is part of the design. This quick test helps confirm that the saved file, security settings, and event-based VBA code are all working together correctly.
Frequently Asked Questions
How do I make Excel add a timestamp automatically when I enter data in a row?
Use the worksheet’s Worksheet_Change event and check whether the changed cell is in the input column you care about. If it is, write Now to a timestamp cell in the same row, such as column B when data is entered in column A. Disable events briefly with Application.EnableEvents = False while writing the timestamp so the macro does not trigger itself repeatedly.
How can I stop VBA from changing an existing timestamp every time I edit the row?
Before writing the timestamp, check whether the timestamp cell is already blank. For example, only add Now if the related timestamp cell has no value yet. This keeps the original creation time instead of replacing it with the latest edit time.
Can I create separate timestamps for created date and last modified date?
Yes. Use one column for the original created timestamp and another column for the last modified timestamp. In your Worksheet_Change code, write to the created timestamp only if it is blank, but update the modified timestamp every time the watched data range changes.
What date and time format should I use in the VBA timestamp?
The safest approach is to store the actual date/time value with Now, then format the cell separately using NumberFormat. Common formats include "yyyy-mm-dd hh:mm:ss" for a sortable timestamp or "mm/dd/yyyy h:mm AM/PM" for a more familiar display. Avoid storing timestamps as plain text unless you do not need to sort, filter, or calculate with them later.
Why does my timestamp macro stop working after I save or reopen the workbook?
The workbook must be saved as a macro-enabled file, usually with the .xlsm extension. When reopening it, macros must also be enabled in Excel’s security prompt or trusted location settings. If the code is event-based, make sure it is placed in the correct worksheet module rather than a standard module.
Bottom Line
VBA is one of the most reliable ways to create automatic date and time stamps in Excel, especially when you need timestamps to appear only after specific data changes. By using worksheet events, proper date/time formatting, and simple safeguards, you can build a tracking system that updates consistently without manual entry.
Recommended Free Tools
Before using the macro in an workbook, test it on a copy, confirm which cells should trigger the timestamp, and decide whether existing timestamps should be protected from overwriting. Once it works as expected, save the file as a macro-enabled workbook and keep macros enabled for the automation to run.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.




