Data Import & Export

How to Split Imported Excel Date and Time Values Without Changing Locale

Amos Gikunda Oct 12, 2026 868 views

Question details

The user needs to import a CSV or XLS file with American date/time formats and split them into separate columns without their regional settings misinterpreting the dates.

How to Split Imported Excel Date and Time Values Without Changing Locale

Product: Excel
Device & OS: not provided
Scenario: Importing CSV or XLS reports containing combined MM/DD/YYYY HH:MM:SS date and time values into a system configured with different regional settings, such as Australian English.
Observed behavior: Ambiguous dates are incorrectly interpreted by the system's local regional settings as DD/MM/YYYY, corrupting the data and causing issues when attempting to split the values while preserving accurate AM/PM designations.

Before you start

Ensure you know the original locale or regional format (e.g., English - United States) of the CSV file before importing, as this will be required to configure the import tool correctly.

Solution 1Recommended

Use Power Query to Set the Import Locale and Split Columns

Use the Power Query Editor to define the correct source locale during the data import process before splitting the combined date and time.

Power Query is the most reliable tool for handling imported text data with conflicting regional formats. By assigning the specific locale of the source data during the import phase, you prevent Excel from automatically guessing and scrambling the dates based on your local system settings.

Import data via Power Query

Go to the 'Data' tab on the Excel ribbon, select 'From Text/CSV', locate your report file, and click 'Transform Data' to open the Power Query Editor.

Apply the correct source locale

In the Power Query Editor, right-click the header of the date/time column. Select 'Change Type', and then click 'Using Locale...'. In the dialog box, set the Data Type to 'Date/Time' and choose the Locale that matches the source file, such as 'English (United States)'.

Split the column by delimiter

With the correctly formatted date/time column selected, go to the 'Transform' tab and click 'Split Column', then choose 'By Delimiter'. Select 'Space' as your delimiter to separate the date from the time values.

Verify formatting and load data

Check the new columns to ensure dates are correct and the time column accurately preserves the AM/PM designation. Finally, click 'Close & Load' on the Home tab to export the cleaned data to your spreadsheet.

Use Power Query to Set the Import Locale and Split Columns

Locale Preservation: Using Power Query's 'Using Locale' feature modifies how Excel reads the incoming data without actually changing your computer's global regional settings.

Easily Import and Split Date and Time in WPS Spreadsheet

WPS Spreadsheet provides robust and intuitive tools to import text data, adjust data types dynamically, and split columns effortlessly. It's an excellent solution for managing CSV reports with mixed regional formats.

1. Open CSV in WPS Spreadsheet: Launch WPS Spreadsheet, go to the 'Data' tab, and select 'Import Data' to locate and open your CSV or XLS file.
2. Configure the Text Import Wizard: During the import process, ensure you specify the column data format as 'Date' and choose the format matching your source (e.g., MDY) to prevent data corruption.
3. Use Text to Columns: Select the newly imported column containing the combined date and time, navigate to the 'Data' tab, and click 'Text to Columns'.
4. Split by Space: Choose 'Delimited', click 'Next', check the 'Space' delimiter option, and finish the wizard to split the data into separate Date and Time columns while preserving the correct formatting.

Seamless and full compatibility with Microsoft Excel (.xlsx) formats.Easy-to-use Text to Columns wizard for accurately splitting date and time values.Free, lightweight, and fast performance when handling large CSV datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my imported Excel dates switch months and days?

This occurs when the source data uses a different date format (such as MM/DD/YYYY) than your system's default regional settings (such as DD/MM/YYYY). Excel attempts to interpret ambiguous dates (like 05/06/2023) based on your local settings, leading to inverted months and days.

Can I fix the date formats using standard Excel formulas instead of Power Query?

Yes, you can use text extraction formulas like DATE, VALUE, LEFT, MID, and RIGHT to manually parse and reconstruct dates. However, using Power Query's 'Using Locale' feature is highly recommended as it is faster, more accurate, and less prone to manual error.

How do I change the default date display format in my spreadsheet?

You can customize how dates are displayed by selecting the relevant cells, right-clicking, and choosing 'Format Cells'. Navigate to the 'Number' tab, select 'Date' from the category list, and pick your preferred layout, or use 'Custom' for specialized formats.

Excel & Data Problems