'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Incorrect Excel Dates Caused by Regional Date Formats
Question details
Imported or downloaded dates are interpreted incorrectly because the source application uses a date format that conflicts with the computer's regional settings.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Importing a downloaded report or opening a raw data file containing regional date values (e.g., DD/MM/YYYY vs MM/DD/YYYY).
- Observed behavior
- Dates display incorrectly with swapped months and days, or the application fails to recognize them as dates, treating them as plain text instead of serial numbers.
Identify the original date format of the source file (e.g., DD/MM/YYYY) before importing. Avoid opening raw CSV or text files directly by double-clicking, as this forces the software to guess the format based on your default system settings.
Use Power Query to Import and Convert Dates by Locale
Power Query provides the most robust way to specify a source file's locale, preventing regional format conflicts during import.
By utilizing Power Query, you can bypass your computer's default regional settings and explicitly tell the application how to read the incoming date strings.
Open a blank workbook, navigate to the Data tab, select 'Get Data', then choose 'From File' followed by 'From Text/CSV'.
Locate your source file and click Import. When the preview window appears, click the 'Transform Data' button.
In the Power Query Editor, right-click the header of the column containing the problematic dates, hover over 'Change Type', and select 'Using Locale'.
In the dialog box, set the Data Type to 'Date' and choose the Locale that matches your source data (for example, English (UK) if the source is DD/MM/YYYY). Click OK.
Click the 'Close & Load' button in the top left corner to output the correctly interpreted serial dates into your spreadsheet.
Use the Text Import Wizard
The Text Import Wizard is a classic and effective method to define the structure of incoming dates for CSV and text files.
Easily Import and Fix Mismatched Dates with WPS Spreadsheet
WPS Spreadsheet provides powerful data handling tools, including an intuitive import wizard that effortlessly resolves regional date conflicts without complex workarounds.
- 1. Open the Data Tab: Launch WPS Spreadsheet, open a new workbook, and navigate to the 'Data' tab on the top ribbon.
- 2. Import Your File: Click on 'Import Data' to locate and open the source file containing the conflicting regional dates.
- 3. Define the Date Sequence: Follow the prompts in the Data Import Wizard. When configuring column types, explicitly set your date column to match the source's sequence (e.g., DMY or MDY).
- 4. Finalize Import: Complete the wizard to automatically parse the raw text into accurate, calculable serial dates.
Frequently Asked Questions
Why do my imported dates sometimes switch the day and month?
This occurs when your computer's regional settings (e.g., MM/DD/YYYY) conflict with the structure of the data you are importing (e.g., DD/MM/YYYY). The spreadsheet interprets the first set of numbers as the month, leading to swapped dates or invalid text values if the day exceeds 12.
Can I fix dates that have already been imported incorrectly as text?
Yes, you can use the 'Text to Columns' feature. Highlight the column with the problematic dates, go to the Data tab, and click 'Text to Columns'. Skip to Step 3, choose 'Date', select the format the text is currently in (e.g., DMY), and click Finish.
How do I change my computer's default regional date settings?
On Windows, navigate to Settings > Time & Language > Region. Under 'Regional format', you can alter the default short date pattern. Keep in mind that changing this will affect how dates are displayed by default across all applications on your device.
What is the difference between a text date and an Excel serial number?
Spreadsheet applications store dates natively as serial numbers (e.g., 44197 for Jan 1, 2021) which allows you to perform calculations and sorting. Text dates are merely character strings that look like dates but cannot be used in formulas or properly sorted.
