How to Automatically Add Today's Date When an Excel Row Changes
Question details
The user needs a way to automatically insert a permanent last-modified date in a specific column whenever any data within that same row is edited.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking modifications or updates to rows of data within a spreadsheet without manually entering the date each time.
- Observed behavior
- Standard worksheet formulas update dynamically, which prevents them from keeping a permanent static timestamp of the edit. A VBA solution is required to generate a static date automatically upon editing.
Ensure that your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that macro execution is allowed in your spreadsheet security settings.
Use a Worksheet Change-Event VBA Macro
Implement a custom VBA script that detects edits in a row and automatically writes the current date into a designated column.
Standard Excel functions like =TODAY() are dynamic and will recalculate to the current date every time you open the file. This makes them unsuitable for a fixed last-modified timestamp.
A VBA Worksheet Change-Event macro provides a permanent timestamp by listening for edits on the worksheet and entering the date as a static text or numeric value.
Right-click the worksheet tab at the bottom of your Excel window and select 'View Code' from the context menu to open the Visual Basic for Applications editor.
In the code window, paste a Private Sub Worksheet_Change(ByVal Target As Range) macro. Ensure the code is set to monitor your data columns and target a specific output column (like Column A) using an instruction such as Cells(Target.Row, 1).Value = Date.
Close the VBA editor and go to File > Save As. Choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown list.
Automate Your Workflows with WPS Spreadsheet
WPS Office offers robust spreadsheet capabilities with full support for advanced formulas and macros, enabling you to automate repetitive tasks like date tracking effortlessly.
- 1. Open your file in WPS Office: Launch WPS Office and open your .xlsm workbook in WPS Spreadsheet.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab in the ribbon and click on 'VB Editor' to inspect or manage your macros.
- 3. Enable Macros: If prompted by a security warning below the ribbon, click 'Enable Macros' to allow your date-stamping code to run.
- 4. Edit rows seamlessly: Make changes to your worksheet; the date will automatically populate in your designated column exactly as intended.
Frequently Asked Questions
Why does the =TODAY() formula not work for a last-modified date?
The =TODAY() formula is dynamic and recalculates every time the worksheet is opened or changed. This means it will always show the current date instead of locking in the date the specific row was actually edited.
Can I track the exact time of the edit along with the date?
Yes. In your VBA code, you can use the 'Now' function instead of 'Date'. This will insert both the current date and the exact time the change was made into the target cell.
Why is my VBA macro not running when I edit a cell?
This usually happens if macro security settings disable macros by default, or if the file was not saved as a Macro-Enabled Workbook (.xlsm). Ensure macros are enabled in your Trust Center or Developer settings and check that the code is placed on the specific Worksheet object, not in a standard Module.
