Fix Excel Date Columns Not Importing to Microsoft Lists Correctly
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.

- 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.
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.
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.
Open your spreadsheet file and click the column header letter (e.g., Column A) to highlight the entire column containing your date values.
Right-click on the highlighted column and select 'Format Cells' from the context menu, or press Ctrl+1 on your keyboard.
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'.
Repeat this exact process for any columns containing numbers or monetary values, ensuring you explicitly set them to 'Number' or 'Currency' instead of 'General'.
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.

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. Open your file in WPS Spreadsheet: Launch WPS Office and open the Excel workbook you intend to import into Microsoft Lists.
- 2. Highlight the date column: Click the top of the column to select all the dates that need formatting.
- 3. Change cell format: Right-click the selection, click 'Format Cells', navigate to the 'Number' tab, and explicitly choose 'Date'.
- 4. Save as .xlsx: Click 'Save' to preserve your column formatting. Your file is now ready for a flawless Microsoft Lists import.

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.




