VBA & Macro Problems

How to Insert a Permanent Date and Time Stamp in Excel Using VBA

Maira Mehtab Oct 22, 2026 868 views

Question details

The user needs to insert a static date and time stamp in an Excel template that remains unchanged, unlike the dynamically updating NOW() function.

How to Insert a Permanent Date and Time Stamp in an Excel Template

Product: Excel
Device & OS: not provided
Scenario: Setting up an Excel template where a permanent timestamp must be recorded as a value upon specific actions, without being altered on subsequent file openings or workbook recalculations.
Observed behavior: Standard Excel formulas like NOW() or TODAY() continuously update whenever the workbook recalculates. The user requires the timestamp to be written exactly once as a static value.

Before you start

Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) or Macro-Enabled Template (.xltm) so that VBA code can run properly. Note down any worksheet protection passwords you plan to use in the macro.

Solution 1Recommended

Use VBA to Insert a Static Timestamp and Protect the Cell

Run a VBA macro that checks if the target cell is empty, writes the current date and time as a static text value, and protects the cell from user edits.

By utilizing Visual Basic for Applications (VBA), you can instruct Excel to fetch the current date and time and paste it strictly as a value rather than a formula. The script below unprotects the sheet, writes the timestamp if the cell is empty, and immediately reapplies protection so users cannot modify the stamped date.

Open the VBA Editor

Press ALT + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor in your active workbook.

Insert a New Module

In the Project Explorer pane on the left, right-click on your workbook name, select 'Insert', and choose 'Module'.

Paste the Timestamp Macro

Copy and paste the following macro code into the module window: Sub AddStartDate() With ThisWorkbook.Worksheets(1) If Len(.Range("G2").Value) = 0 Then .Unprotect Password:="YourPassword" .Range("G2").Value = Format(Now, "MMMM dd yyyy hh:mm AM/PM") .Protect Password:="YourPassword" End If End With End Sub Ensure you update 'G2' to your target cell and replace 'YourPassword' with your actual worksheet password.

Execute the Macro

Press F5 or go to Run > Run Sub/UserForm to execute the code. The current date and time will be permanently inserted into the specified cell.

Automating the Macro: If you want this timestamp to populate automatically when the file is opened, you can call the `AddStartDate` procedure from the `Workbook_Open` event inside the `ThisWorkbook` module.

Advanced Macro Automation

Use WPS Spreadsheet for Advanced Macro Automation

WPS Office provides robust support for VBA macros, allowing you to easily automate tasks like inserting permanent timestamps in your templates. Enjoy a familiar interface and seamless compatibility with Microsoft Excel macros.

1. Enable the Developer Tab: Open your workbook in WPS Spreadsheet, go to the top ribbon, and click on the 'Developer' tab to access advanced macro features.
2. Open the VBA Editor: Click the 'Visual Basic' button to open the VBA editor where you can manage your macros.
3. Insert the Timestamp Code: Insert a new module, paste your timestamp macro, and save your document as a macro-enabled workbook to retain the automation.

Full compatibility with Microsoft Excel .xlsm and .xltm formatsBuilt-in Developer tools for editing and running VBA macrosLightweight application with high processing performanceFamiliar ribbon interface for an easy and seamless transition

microsoft office alternative - wps office

Frequently Asked Questions

Why does my date change when I use the NOW() formula?

The NOW() and TODAY() functions are 'volatile' formulas in Excel. This means they recalculate and update to the current system time whenever the worksheet calculates or the file is reopened. To keep the date static, it must be inputted as a raw value.

How can I manually insert a static date and time without VBA?

You can use keyboard shortcuts. Select a cell, press 'Ctrl + ;' to insert the current date, type a Space, and then press 'Ctrl + Shift + ;' to insert the current time. This combination inserts permanent values that will not update.

What if I get an error when the macro tries to write the date?

If your worksheet is already protected and the macro doesn't have the correct password to unprotect it first, it will fail with an error. Ensure the `Unprotect Password:="YourPassword"` line exactly matches your sheet's active protection password.

How do I prevent the macro from overwriting an existing timestamp?

The provided code uses the `If Len(.Range("G2").Value) = 0 Then` logic. This checks if the cell is completely empty. If a date is already present, the length is greater than 0, and the macro will safely skip overwriting the existing data.

Excel & Data Problems