Data Import & Export

Import Mixed CSV Data and Date-Time Values in Excel

Elise WilliamsElise Williams Nov 20, 2026 869 views

Question details

Import a CSV containing date-time, text, and numeric fields into Excel while preserving the headers and usable date-time values.

Import Mixed CSV Data and Date-Time Values in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to import mixed CSV data and use the date-time column as the X values in an XY Scatter chart.
Observed behavior
Get & Transform may treat the date-time column as text, and the resulting chart can show serial numbers or dates near 1900.
Before you start

Open the CSV in a text editor and check its delimiter, header rows, and date order, such as month/day/year or day/month/year. Check whether the date and time appear together in the same field so you can verify both after import.

Solution 1Recommended

Import the CSV with the legacy Text Import Wizard

Use the legacy wizard to specify the date order during import and preserve the column headers.

1
Enable the legacy wizard

In Excel, open File > Options > Data. Under Show legacy data import wizards, select From Text (Legacy), then click OK. The option may be located differently in older Excel versions.

2
Open the CSV

Go to Data > Get Data > Legacy Wizards > From Text (Legacy), select the CSV file, and click Import.

3
Set the delimiter and header

In the Text Import Wizard, choose Delimited and select the CSV's delimiter, such as Comma. If the file has a header row, select My data has headers when that option is available.

4
Set column formats

In the column preview, select the date-time column, choose Date, and select the matching date order, such as MDY or DMY. Set descriptive columns to Text and numeric columns to General, then click Finish and choose where to place the data.

5
Verify and chart the data

Select the imported date-time cells and apply a date-time format from Home > Number. Confirm that both dates and times are correct, then select the date-time and numeric columns and choose Insert > Scatter (X, Y).

Import the CSV with the legacy Text Import Wizard
Check the imported values: The wizard may not recognize every date-time format. If values remain text or the time is lost, use Power Query with the matching locale instead.
Free Microsoft Office alternative

Work with CSV data in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening CSV files and working with mixed data. It supports common Excel formats to make moving existing workbooks easier, though it does not change Excel's import behavior.

Open CSV data and work with date-time, text, and numeric columns.Compatible with Microsoft Excel formats, including XLSX and XLS.Use a familiar spreadsheet interface for a smoother transition from Excel.Free and lightweight for everyday spreadsheet work.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my scatter chart show dates near 1900?

Excel stores dates as serial numbers, so small or incorrectly interpreted X values can appear as dates near 1900. Check that the chart's X values reference the imported date-time column and that the values were converted correctly.

Why does the chart show numbers instead of dates?

The X-axis may be displaying Excel's underlying date serial numbers. Right-click the axis, choose Format Axis > Number, and apply a date or custom date-time format.

How do I preserve column names in Power Query?

In the Power Query editor, remove any descriptive rows above the column names with Home > Remove Rows > Remove Top Rows. Then use Home > Use First Row as Headers if the names have not already been promoted.

Which locale should I choose for date-time conversion?

Choose a locale whose date order matches the CSV. For example, use an appropriate US locale for month/day/year data or a UK locale for day/month/year data. In Power Query, right-click the column and select Change Type > Using Locale.

Will changing the cell format convert text into real dates?

No. Home > Number changes how recognized values are displayed but does not reliably convert date-time text. Reimport the column with the legacy wizard or convert it in Power Query using the correct locale.