Data Import & Export

How to Fix Day and Month Date Format Errors in Excel

Maira Mehtab Sep 22, 2026 875 views

Question details

The user needs to correct inverted date formats where the spreadsheet interprets the day as the month, or vice versa.

Product: Excel
Device & OS: not provided
Scenario: Importing or opening data where the source regional date format does not match the system's regional settings.
Observed behavior: Dates such as 6/1/2025 are displayed as June 1 instead of January 6, causing data inaccuracies.

Before you start

Verify your computer's system regional settings and identify the original date format (e.g., DD/MM/YYYY vs. MM/DD/YYYY) of your source data before attempting to convert it.

Solution 1Recommended

Use Text to Columns to Fix Date Formats

This is the fastest method to quickly reformat an entire column of inverted dates back to your preferred regional setting.

Select the data

Highlight the entire column containing the problematic dates that need to be fixed.

Open Text to Columns

Navigate to the Data tab on the ribbon and click on the Text to Columns button.

Navigate the wizard

Choose the Delimited option and click Next twice to reach the third step of the wizard.

Select the source date format

Under the Column data format section, select Date and choose the format of your source data from the dropdown (e.g., select DMY if the source data is Day-Month-Year).

Apply formatting

Click Finish to apply the correct formatting and update the column.

Solution 2

Import Data Correctly Using Power Query

Ideal for recurring imports, Power Query allows you to specify the exact locale of the source data during the import process.

Easily Fix Date Format Errors with WPS Spreadsheet

WPS Office Spreadsheet provides an intuitive Text to Columns wizard, making it incredibly simple to resolve date format mismatches and effectively manage imported datasets.

1. Open your file: Launch WPS Spreadsheet and open the file containing the incorrect dates.
2. Access Text to Columns: Select the column with the dates, navigate to the Data tab, and click Text to Columns.
3. Proceed to step 3: Select Delimited and click Next until you reach Step 3 of the wizard.
4. Specify correct format: Select the Date option, choose the original source format (like DMY), and click Finish.

Seamlessly compatible with Microsoft Excel (.xlsx, .xls, .csv) formats.Intuitive Text to Columns wizard to quickly fix imported data formats in seconds.Lightweight, fast, and completely free to use for everyday spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel switch my days and months?

This happens when the imported data's date format (e.g., DD/MM/YYYY) conflicts with your computer's system regional settings (e.g., MM/DD/YYYY). Excel attempts to read the first number as the month if the number falls between 1 and 12.

How can I change the default date format in my spreadsheet?

You can change how dates are displayed by selecting the cells, right-clicking, and choosing 'Format Cells'. Under the 'Number' tab, select 'Date' and pick your preferred regional format or locale.

Will using Text to Columns alter the actual data values?

No. Using the Text to Columns method corrects the underlying serial number value that the spreadsheet uses to interpret dates, ensuring the data is accurately recorded and displayed without changing the original intent.

Excel & Data Problems