Fix CSV Dates Imported as Numbers in Excel Power Query
Question details
Convert CSV date values to valid dates without losing their original day, month, and year.

- 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.
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.
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.
In Excel, select Data > From Text/CSV, choose the CSV file, and select Transform Data.
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.
Select the affected column, then choose Transform > Data Type > Text. If prompted, add a new type conversion step.
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.
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.

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.

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.




