SharePoint Document Issues

Fix Excel Date Columns Not Importing to Microsoft Lists Correctly

Ayan MasoodAyan Masood Oct 14, 2026 869 views

Question details

The user needs to import an Excel file into Microsoft Lists or SharePoint without date columns converting into plain text or 5-digit serial numbers.

How to Fix Excel Date Columns When Importing to Microsoft Lists
Product
Excel, Microsoft Lists, SharePoint
Device & OS
not provided
Scenario
Creating a new Microsoft List or SharePoint Online list by importing data from an existing Excel workbook.
Observed behavior
During import, date values appear as sequential numbers or text, the 'Date and Time' column type is unavailable, and attempting to change the column type after creation causes data loss.
Before you start

Ensure you have the desktop version of your spreadsheet editor open and have edit access to the source Excel file, as you will need to modify column properties before importing.

Solution 1Recommended

Explicitly Format Excel Columns Before Importing

Pre-formatting the date, number, and currency columns in Excel ensures that Microsoft Lists and SharePoint correctly recognize the data types during the import process.

By default, Excel often leaves column formats set to 'General'. When Microsoft Lists or SharePoint reads a 'General' column, it imports dates as their underlying serial numbers or plain text. Explicitly defining the format in Excel forces the importer to provide the correct 'Date and Time' mapping options.

1
Select the target column

Open your spreadsheet file and click the column header letter (e.g., Column A) to highlight the entire column containing your date values.

2
Open Format Cells

Right-click on the highlighted column and select 'Format Cells' from the context menu, or press Ctrl+1 on your keyboard.

3
Apply Date format

In the 'Number' tab, select 'Date' from the Category list on the left. Choose your preferred date style from the right pane and click 'OK'.

4
Format Number and Currency columns

Repeat this exact process for any columns containing numbers or monetary values, ensuring you explicitly set them to 'Number' or 'Currency' instead of 'General'.

5
Save and import

Save your Excel file. Navigate to Microsoft Lists or SharePoint Online, click 'New List', choose 'From Excel', and select your updated file. The 'Date and Time' column type will now be available.

Explicitly Format Excel Columns Before Importing
Data Preserved: Setting the format explicitly prevents the need to change column types after the list is created, entirely avoiding the risk of deleting existing imported data.
Free Spreadsheet Editor

Format Your Spreadsheets Perfectly with WPS Office

WPS Spreadsheet provides a seamless and intuitive way to format date, number, and currency columns. Easily prepare your .xlsx files to ensure flawless data imports into Microsoft Lists and SharePoint Online.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the Excel workbook you intend to import into Microsoft Lists.
  2. 2. Highlight the date column: Click the top of the column to select all the dates that need formatting.
  3. 3. Change cell format: Right-click the selection, click 'Format Cells', navigate to the 'Number' tab, and explicitly choose 'Date'.
  4. 4. Save as .xlsx: Click 'Save' to preserve your column formatting. Your file is now ready for a flawless Microsoft Lists import.
Fully compatible with Microsoft Excel (.xlsx, .xls) formatsFamiliar formatting menus with zero learning curveLightweight application that handles large datasets quicklyCompletely free to use for your daily spreadsheet formatting
microsoft office alternative - wps office

Frequently Asked Questions

Why do Excel dates appear as 5-digit numbers in Microsoft Lists?

Spreadsheet software stores dates as sequential serial numbers for calculation purposes. If the column is set to a 'General' format instead of an explicit 'Date' format, Microsoft Lists simply imports that underlying 5-digit serial number.

Can I change a text column to a Date and Time column after creating the list?

While Microsoft Lists allows you to change column types via List Settings, doing this for imported text or numbers often results in the existing data being erased or corrupted. It is highly recommended to format the data in Excel before importing.

Does this formatting rule apply to SharePoint Online lists as well?

Yes. Microsoft Lists and SharePoint Online share the same underlying list infrastructure. You must explicitly format date, number, and currency columns in your Excel file before importing to either platform.