Data Import & Export

Fix CSV Dates Imported as Numbers in Excel Power Query

John WilsonJohn Wilson Nov 18, 2026 870 views

Question details

Convert CSV date values to valid dates without losing their original day, month, and year.

Fix CSV Dates Imported as Numbers in Excel Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
A CSV contains dates such as 09.09.2022 in day.month.year format.
Observed behavior
The imported values lose their separators and are interpreted as numbers or date serial values.
Before you start

Open the original CSV in a text editor and confirm that the affected values still contain separators, such as 09.09.2022. Identify the date order before choosing a locale in Power Query.

Solution 1Recommended

Import the CSV dates as text, then convert them using a locale

Use this method when the original CSV still contains dates in day.month.year format.

1
Open the CSV in Power Query

In Excel, select Data > From Text/CSV, choose the CSV file, and select Transform Data.

2
Remove automatic type conversion

In Power Query Editor, check Applied Steps and delete any Changed Type step that converted the date column incorrectly. Confirm that the column preview again shows the original values with separators.

3
Set the column to text

Select the affected column, then choose Transform > Data Type > Text. If prompted, add a new type conversion step.

4
Convert using the correct locale

Right-click the column header and select Change Type > Using Locale. Choose Date as the data type and a locale that uses day.month.year dates, such as German (Germany), then select OK.

5
Check and load the dates

Review several values in the preview, including dates where the day is 12 or less, to verify that the day and month are correct. Select Home > Close & Load to return the results to Excel.

Import the CSV dates as text, then convert them using a locale
Formatting cannot recover lost information: Changing a number's cell format to Date treats it as a date serial; it does not reconstruct the original CSV date. If the separators are already missing from the source, the original date may be ambiguous.
Free Microsoft Office alternative

Work with CSV files in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move existing files. It does not repair Microsoft Excel Power Query imports.

Open and review CSV data in WPS Spreadsheets.Work with Microsoft Excel formats, including XLSX and XLS.Use a familiar spreadsheet interface for an easier transition from Excel.Get a lightweight office suite with free tools.
microsoft office alternative - wps office

Frequently Asked Questions

Why does formatting the number as a date show the wrong year?

Excel interprets the number as a date serial rather than rebuilding the original day, month, and year. Reimport the original CSV in Power Query and convert the preserved text using the correct locale.

Which locale should I use for 09.09.2022?

In Power Query Editor, right-click the date column and select Change Type > Using Locale. Choose Date and a locale that recognizes day.month.year, such as German (Germany).

What if a Changed Type step has already removed the separators?

Under Applied Steps, delete the incorrect Changed Type step and check whether the original text reappears. Then set the column to Text before converting it to Date using a locale.

Can I recover the date if the CSV itself contains only numbers?

Possibly, but a value without separators may be ambiguous, especially if leading zeros were lost. Check the original data source or its date-format rules before attempting a reconstruction.