VBA & Macro Problems

How to Fix Excel Macros Recording Fixed Dates Instead of Current Date and Time

Muhammad Talha Sep 28, 2026 869 views

Question details

The user needs a macro to insert the current date and time upon execution, but the macro recorder captures fixed historical date and time values when using keyboard shortcuts.

How to Fix Excel Macros Recording Fixed Dates Instead of Current Date and Time

Product: Microsoft Excel
Device & OS: not provided
Scenario: Recording a macro using the keyboard shortcuts Ctrl+; for date and Ctrl+Shift+; for time.
Observed behavior: The recorded macro outputs the exact date and time from when the macro was originally recorded, rather than updating to the current date and time when the macro is executed.

Before you start

Ensure that the Developer tab is enabled in your spreadsheet ribbon so you can easily access the Visual Basic Editor to modify your recorded macros.

Solution 1Recommended

Modify the Recorded Macro to Use VBA Date and Time Functions

Manually update the recorded VBA code to dynamically fetch the current date and time using built-in VBA variables instead of hardcoded strings.

When you use shortcuts like Ctrl+; during macro recording, Excel captures the resulting text value rather than the keystroke. To make the macro output the current date every time it runs, you must replace these fixed values with the dynamic VBA 'Date' and 'Time' functions.

Open the Visual Basic Editor

Press Alt + F11 on your keyboard, or navigate to the Developer tab and click on 'Visual Basic'.

Locate Your Recorded Macro

In the left-hand Project Explorer pane, expand the 'Modules' folder and double-click 'Module1' (or the module containing your macro) to view the code.

Replace Fixed Values with Functions

Find the lines of code where the fixed date and time are inserted (e.g., ActiveCell.FormulaR1C1 = "10/24/2023"). Replace the hardcoded date string with the word Date, and replace the time string with the word Time.

Save and Test the Macro

Click the Save icon, close the Visual Basic Editor, and run your macro again to verify it now inserts the current system date and time.

Modify the Recorded Macro to Use VBA Date and Time Functions

Example VBA Code: Your updated code should look like this: Sub Macro1() Range("E1").Select ActiveCell.FormulaR1C1 = Date Range("E2").Select ActiveCell.FormulaR1C1 = Time End Sub

Advanced VBA Support

Edit and Run Macros Seamlessly with WPS Office

WPS Office provides robust compatibility with Microsoft Excel macros and VBA scripts. You can easily record, edit, and execute your macros to automate dynamic date and time insertions without hassle.

1. Open Your File in WPS Spreadsheet: Launch WPS Office and open the workbook containing your recorded macro.
2. Access the Macro Editor: Navigate to the Developer tab on the ribbon and click on 'Macros' or 'Visual Basic' to open the editor.
3. Modify the VBA Code: Locate your macro script and replace the static date/time strings with the dynamic 'Date' and 'Time' variables.
4. Run Your Automated Task: Save your changes and execute the macro directly from the WPS Spreadsheet interface to instantly insert the current date.

Fully compatible with Microsoft Excel VBA syntax and macro functionalityBuilt-in Visual Basic Editor for effortless code modificationsHighly compatible with .xls, .xlsx, and .xlsm file formatsLightweight application with a familiar, easy-to-use interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro recorder capture fixed dates instead of shortcuts?

The macro recorder is designed to capture the literal output value generated by your actions. When you press the date shortcut, Excel immediately generates a static text string of that date, which is what the recorder writes into the VBA code.

Can I use a formula instead of a VBA macro for a dynamic date?

Yes. If you prefer not to use macros, you can type the formula =TODAY() into a cell for the current date, or =NOW() for the current date and time. Keep in mind that these formulas will continuously update every time the spreadsheet recalculates, unlike a macro which inserts a static timestamp when run.

How do I enable the Developer tab to edit my macros?

To enable the Developer tab, go to File > Options > Customize Ribbon. In the right pane, check the box next to 'Developer', and then click OK. The tab will now appear on your main ribbon.

Excel & Data Problems