VBA & Macro Problems

Fix Incorrect CSV Dates in Excel VBA Imports

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to prevent Excel VBA from automatically changing CSV date formats from dd/mm/yyyy to mm/dd/yyyy during the file import process.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing bank CSV files into a workbook using an automated VBA macro.
Observed behavior: Excel VBA interprets the imported dates using a US-centric locale by default, forcibly converting the original dd/mm/yyyy dates into mm/dd/yyyy format.

Before you start

Before modifying your VBA script, ensure you have backed up your original macro workbook and a sample bank CSV file to safely test the new import code without losing data.

Solution 1Recommended

Rename CSV to TXT and Use the Text Import Wizard

This method bypasses Excel's default US-centric date parsing by forcing the application to treat the file as raw text, allowing you to explicitly define the date format for specific columns during import.

Because VBA defaults to US regional settings when opening .csv files directly, standard methods often fail to preserve the Day/Month/Year structure. Changing the file extension to .txt triggers the Text Import Wizard, granting you full control over column data formats.

Manually rename the file extension

Locate a copy of your bank CSV file in Windows File Explorer. Right-click the file, select 'Rename', and change the '.csv' extension to '.txt'.

Record the text import process

Open Excel, navigate to the 'View' tab, click 'Macros', and select 'Record Macro'. Next, go to the 'Data' tab, click 'From Text/CSV', and browse to select your renamed .txt file to launch the Text Import Wizard.

Set the specific column date format

Proceed to step 3 of the Text Import Wizard. Click on the column containing your dates, select the 'Date' radio button under 'Column data format', and choose 'DMY' (dd/mm/yyyy) from the dropdown list. Click 'Finish' to complete the import.

Incorporate the code into your macro

Stop recording the macro. Press Alt + F11 to open the VBA Editor, locate the newly recorded macro, and copy the query table import code. Paste this into your main VBA script. You can use the 'Name' statement in VBA (e.g., Name oldPath As newPath) to automate renaming the .csv to .txt before the import code runs.

Automating the Rename Process: You don't need to rename the files manually every time. Use VBA's built-in FileCopy and Name functions to programmatically duplicate and rename the .csv to .txt right before executing your newly recorded import code.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

If you frequently struggle with Excel's regional date formatting and VBA quirks, WPS Office provides a lightweight, highly compatible alternative for handling spreadsheets, CSV files, and complex data formats without the hassle.

1. Download and Install: Visit the official WPS Office website to download and install the free suite on your device.
2. Open your data files: Launch WPS Spreadsheet and easily open your CSV or Excel workbooks directly from the main dashboard.
3. Process data easily: Enjoy highly compatible data processing, formatting, and layout tools without the heavy subscription costs.

Free, lightweight, and fast Office suiteExcellent compatibility with Microsoft Excel formats (.xlsx, .csv, .xlsm)Intuitive interface for simpler data importing and formattingSeamless migration from Microsoft Office with no learning curve

Frequently Asked Questions

Why does Excel VBA automatically change my CSV date formats?

Excel VBA is inherently US-centric. When it directly opens a CSV file, it overrides your system's local regional settings and processes dates using the US regional format (mm/dd/yyyy) by default.

Can I use Local:=True in my VBA code to fix the date format issue?

While adding Local:=True to the Workbooks.Open method helps in some scenarios, it is notoriously unreliable for CSV files opened directly via VBA. Renaming the file to .txt and defining the format explicitly is a much safer and more consistent workaround.

Do I have to manually rename the CSV file every time I run the macro?

No. You can automate this process within your VBA macro using the 'Name' statement (e.g., Name "C:\path\file.csv" As "C:\path\file.txt") to programmatically rename the file before the import code executes.

Excel & Data Problems