VBA & Macro Problems

How to Convert Dates with Periods or Numbers into Real Excel Dates

Maira Mehtab Sep 22, 2026 869 views

Question details

Users need a method to automatically convert non-standard shorthand date inputs (such as 1.1.25 or 010125) into recognizable Excel date formats.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Entering dates quickly using continuous numbers or periods instead of standard slashes or hyphens during data entry.
Observed behavior: Excel fails to recognize period-separated or continuous numeric entries as dates, storing them as text or general numbers, which disrupts sorting, filtering, and conditional formatting.

Before you start

Ensure you are using the desktop version of your spreadsheet software, as VBA macros are not supported in web-based or mobile applications.

Solution 1Recommended

Use a VBA Worksheet_Change Macro

Apply a custom VBA script to automatically translate period-separated or numeric strings into native, correctly formatted dates upon data entry.

By utilizing the Worksheet_Change event in VBA, you can instruct the spreadsheet to intercept specific text or numeric inputs and convert them into actual date values instantly. This ensures your data remains fully functional for sorting and filtering.

Open the VBA Editor

Press 'Alt + F11' on your keyboard while in your desktop spreadsheet application to open the Visual Basic for Applications (VBA) editor.

Access the Worksheet Module

In the Project Explorer pane on the left side of the VBA editor, double-click the specific worksheet name (e.g., Sheet1) where you want the date conversion to take effect.

Insert the Macro Code

Paste your custom Worksheet_Change macro code into the blank code window. Make sure to define the specific input range (like 'A2:A100') within your code to prevent it from altering unintended cells.

Save as a Macro-Enabled Workbook

Go to File > Save As, and select 'Excel Macro-Enabled Workbook (*.xlsm)' from the file format dropdown to ensure your VBA code is preserved. Verify that macros are enabled in your Trust Center settings.

Platform Limitations: This VBA solution operates exclusively on desktop spreadsheet software. It will not execute in browser-based web apps or mobile versions.

Advanced Spreadsheet Automation

Use WPS Spreadsheet for Seamless Macro and Date Handling

WPS Office offers native and robust support for VBA macros, empowering you to effortlessly run custom date conversion scripts and manage complex datasets.

1. Download and Install WPS Office: Download the free WPS Office suite from the official website, install it, and launch WPS Spreadsheet.
2. Open the Developer Tab: Open your workbook, navigate to the 'Developer' tab on the top ribbon, and click 'VBA Editor' to access the coding environment.
3. Apply and Run Your Macro: Paste your date conversion macro into the target worksheet module, save the file as a macro-enabled workbook (.xlsm), and begin typing your shortcut dates.

Fully compatible with Microsoft Excel formats, including .xlsx, .xlsm, and .csv.Built-in advanced VBA editor to flawlessly execute your Worksheet_Change macros.Highly lightweight installation with an intuitive, user-friendly interface.Completely free and powerful solution for automating repetitive data entry tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel treat dates with periods as text instead of dates?

Excel relies on your computer's regional settings to interpret dates. If your system is set to use slashes or hyphens (like mm/dd/yyyy), periods are not recognized as valid date separators, causing the spreadsheet to interpret the entry as general text.

Can I convert numbers like 010125 into dates without using VBA?

Yes, but it requires manual steps. You can use the 'Text to Columns' wizard in the Data tab and set the column data format to Date (MDY). Alternatively, you can use a combination of DATE, LEFT, MID, and RIGHT formulas in an adjacent column to parse the numbers into a date value.

Why isn't my VBA date conversion macro working in Excel for the web?

Web-based and mobile spreadsheet applications do not support VBA macros due to browser security limitations. To run automated VBA scripts, you must open the workbook in a compatible desktop application like WPS Office or Excel Desktop.

Is it possible to simply change the cell format to display 010125 as a date?

You can apply a Custom Number Format (such as 00\/00\/00) to visually insert slashes, making it look like a date. However, the underlying value remains a standard number, which may still cause issues if you need to perform chronological sorting or date-based filtering.

Excel & Data Problems