How to Insert Current Date and Time as Editable Text Using Excel VBA
Question details
The user wants an Excel VBA macro to insert the current date and time with a trailing hyphen into a cell while ensuring it remains editable as text instead of being auto-formatted by the application.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a VBA macro to log timestamps in a specific MM/DD/YY format without losing trailing characters.
- Observed behavior
- Excel automatically converts the custom timestamp into a standard Date/Time format, stripping out the trailing hyphen and ignoring the intended AM/PM display format.
Ensure you have the Developer tab enabled in your Excel ribbon and that your macro security settings are configured to allow VBA code execution.
Prefix the VBA Format String with an Apostrophe
By adding an apostrophe to the beginning of your VBA format string, you force Excel to treat the inserted timestamp strictly as editable text.
Excel has built-in parsing logic that aggressively converts recognizable date strings into actual Date values. This frequently removes formatting characters like trailing hyphens. Prefixing the result with a single quote (') safely overrides this behavior.
Press ALT + F11 in Excel to open the Visual Basic for Applications editor window.
Right-click on your workbook project in the Project Explorer panel, select Insert, and then click Module.
Paste the following code into the module window: Sub InsertDate() ActiveCell.Value = Format(Now, "'mm/dd/yy h:mm AM/PM - ") End Sub.
Select your target cell in the worksheet, navigate to the Developer tab, click Macros, select InsertDate, and click Run.
Prepend the Timestamp to Existing Cell Data
If you are logging events, you can type your text first and use a modified macro to prepend the current date and time to the existing string.
Automate Your Workflow with WPS Spreadsheet
WPS Spreadsheet offers robust VBA and Macro support, allowing you to easily insert formatted dates, automate tasks, and customize data entry natively. Enjoy a familiar interface while flawlessly executing your existing Excel macros.
- 1. Open File in WPS: Launch WPS Spreadsheet and open your existing macro-enabled workbook.
- 2. Access Developer Tab: Navigate to the Developer tab on the top ribbon and click on the VBA Editor icon.
- 3. Run Custom Macros: Insert your date-formatting VBA code and execute it directly within the cell without compatibility issues.
Frequently Asked Questions
Why does Excel remove the AM/PM or hyphen from my VBA timestamp?
Excel automatically attempts to parse cell values into recognized date and time formats. When it identifies a date pattern, it drops unassociated text elements like trailing hyphens or custom AM/PM indicators unless the cell is explicitly told to behave as text.
How do I automate the timestamp insertion when a cell is edited?
You can use the Worksheet_Change event in VBA. By linking your formatting code to this event, the date and time can automatically populate or be prepended to the text string whenever a user updates a specific range of cells.
Does the leading apostrophe show up when I print the spreadsheet?
No, the leading apostrophe acts strictly as a hidden text-formatting flag. It is visible in the formula bar when the cell is selected, but it will not appear in the cell display or when the spreadsheet document is printed.
