Data Import & Export

Import CSV Dates in the Correct Format in Excel 2016

Natalie TaylorNatalie Taylor Nov 22, 2026 868 views

Question details

Import dates from an existing CSV file in Excel 2016 using the correct month/day/year interpretation.

Import CSV Dates in the Correct Format in Excel 2016
Product
Microsoft Excel 2016 Power Query
Device & OS
not provided
Scenario
A CSV file contains dates in mm/dd/yy format, and the user wants to specify how Excel interprets them during import.
Observed behavior
Excel imports the dates incorrectly, and the expected date-format option is not visible in the initial Power Query import interface.
Before you start

Check that the source date column consistently uses mm/dd/yy and identify any blank or invalid values. Keep a few known dates handy so you can verify the imported results.

Solution 1Recommended

Set the CSV date column type using a locale in Power Query

Use this method to interpret CSV dates as month/day/year before loading them into Excel.

1
Open the CSV import

In Excel, go to Data > Get Data > From Text/CSV and select the CSV file. In some Excel 2016 versions, use Data > New Query > From File > From CSV.

2
Open Power Query Editor

In the import preview, click Edit or Transform Data to open Power Query Editor instead of loading the file immediately.

3
Remove an incorrect type conversion

In Query Settings > Applied Steps, check for an automatically added Changed Type step. If it has already converted the date column incorrectly, remove that step using the X beside it.

4
Choose the date locale

Right-click the date column header and select Change Type > Using Locale. Choose Date as the data type and English (United States) as the locale, then click OK.

5
Verify and load

Check known dates in the preview, including any conversion errors. When the values are correct, select Home > Close & Load.

Set the CSV date column type using a locale in Power Query
Date interpretation and display: The locale controls how the source text is interpreted; it does not necessarily control how dates appear in the worksheet. Two-digit years may also be interpreted using a century cutoff, so verify the resulting years.
Free Microsoft Office alternative

Work with CSV files in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Its Microsoft Excel format support makes it easier to move existing workbooks while working with CSV data.

Open and work with CSV data in WPS SpreadsheetsCompatible with Microsoft Excel formats including XLS and XLSXFamiliar spreadsheet interface for easier migrationFree and lightweight office suite
QA img-9

Frequently Asked Questions

Where is the option to specify the date format during import?

In Power Query Editor, right-click the date column header and choose Change Type > Using Locale. Select Date and a locale that matches the source values.

Which locale should I use for mm/dd/yy dates?

Choose English (United States) in the Using Locale dialog. Verify a few known dates and their years in the preview before selecting Close & Load.

What if Power Query has already converted the dates incorrectly?

In Query Settings > Applied Steps, remove the automatic Changed Type step if it caused the incorrect conversion. Then apply Change Type > Using Locale to the original date text.

Why do the imported dates look different from the CSV?

The locale determines how Power Query reads the source dates, while Excel's cell format determines their appearance. After loading, select the cells and use Format Cells > Date or Custom to change the display.