'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel Dates Imported as Numbers in Microsoft Lists
Question details
The user needs to correct an issue where calendar dates in an Excel workbook display as 5-digit serial numbers when imported into Microsoft Lists.
- Product
- Microsoft Lists / Microsoft Excel
- Device & OS
- Windows 10
- Scenario
- Creating a new Microsoft List by importing an existing Excel workbook containing date columns.
- Observed behavior
- Valid Excel dates (e.g., '6/12/2014 12:00 AM') are incorrectly interpreted and displayed as numerical serial values (e.g., '41802') during the Microsoft Lists review and import stage.
Ensure that your source Excel file is saved and that the date columns are explicitly formatted as 'Date' rather than 'General' before initiating the import.
Change the Column Type During the Import Review Stage
Manually update the column data type within the Microsoft Lists import preview window to force the serial numbers to display as calendar dates.
Spreadsheet applications like Excel store dates as sequential serial numbers for calculation purposes. When importing this data, Microsoft Lists may read the underlying serial number instead of the formatted date.
Changing the column type during the review stage instantly corrects this misinterpretation without requiring you to alter your original Excel file.
Open the Microsoft Lists desktop app or web interface and select the option to create a new list 'From Excel'.
Upload your target Excel workbook. In the customization and review stage, locate the specific column where your dates are displaying as serial numbers (e.g., 41802).
Click on the column header type drop-down (which likely defaults to 'Number' or 'Single line of text') and change it to 'Date and time'.
Verify that the serial numbers in the preview instantly update to display correctly as calendar dates, then proceed to finish creating your list.
Manage Your Spreadsheet Data Seamlessly with WPS Office
If you frequently encounter data formatting issues or simply need a robust, cost-effective way to manage your spreadsheets, consider using WPS Office. It provides a lightweight, highly compatible, and user-friendly spreadsheet environment that perfectly handles complex formats without the heavy subscription fees.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and instantly open your existing Excel workbooks with full format retention.
- 3. Format Data Easily: Use the intuitive cell formatting tools to ensure your dates and numbers are perfectly configured before exporting or importing into other tools.
Frequently Asked Questions
Why does Excel store dates as serial numbers like 41802?
Excel stores dates as sequential serial numbers so they can be easily used in mathematical calculations. By default, January 1, 1900, is stored as serial number 1, and subsequent days are counted from there. For example, 41802 represents exactly 41,802 days after January 1, 1900, which is June 12, 2014.
How can I prevent Excel dates from changing to numbers when importing to other apps?
Before importing your data, you can convert your Excel date column to plain text format using the TEXT function (e.g., =TEXT(A2, "mm/dd/yyyy")). This forces the receiving application to read the date exactly as a text string, preventing it from fetching the underlying serial number.
Can I fix the date format in Microsoft Lists after the list has already been created?
Yes. Navigate to your Microsoft List settings (gear icon), go to List Settings, select the column that shows serial numbers, and change its data type to 'Date and Time'. You may need to refresh your browser view to see the updated formats applied to the existing items.
