Formatting Issues

How to Fix SharePoint List Date and Time Format Errors After Excel Import

Ayan Masood Oct 23, 2026 869 views

Question details

The user needs to resolve date validation errors that occur when importing an Excel list into a SharePoint site.

How to Fix SharePoint List Date and Time Format Errors

Product: SharePoint / Excel
Device & OS: not provided
Scenario: Importing a list of records from an Excel workbook into a SharePoint list.
Observed behavior: SharePoint triggers date validation errors or rejects entries due to incompatible date and time formats mismatched with the site's regional settings.

Before you start

Verify the regional settings of your SharePoint site to understand the exact date and time format the system expects before attempting the import again.

Solution 1Recommended

Match Date Inputs with SharePoint Regional Settings

Align the date and time format in your source data with the format required by your SharePoint site's regional settings to prevent validation errors.

SharePoint validates imported data based on the site's configured regional settings. If your Excel file uses a US date format (MM/DD/YYYY) but SharePoint expects a European format (DD.MM.YYYY), the import will fail or transpose the dates.

Identify SharePoint Regional Settings

In SharePoint, navigate to Site Settings > Regional settings to view the required date format (e.g., DD.MM.YYYY HH:MM).

Format Source Data

Open your source Excel file and format the date column to perfectly match the SharePoint format, such as typing '6.9.2022 00:00'.

Re-import the List

Save the changes to your spreadsheet and use the 'Export Table to SharePoint List' function or manually paste the correctly formatted data.

Match Date Inputs with SharePoint Regional Settings

Successful Import: Matching the regional settings ensures that SharePoint properly recognizes and categorizes your date fields without throwing validation errors.

Solution 2

Create a Calculated Column for Custom Date Formatting

If you need the date displayed in a specific format regardless of site settings, use a calculated column based on the original date field.

Free Microsoft Office alternative

Manage Your Spreadsheets Effortlessly with WPS Office

While SharePoint lists require specific format matching, preparing your data is easy with WPS Office. As a free, lightweight, and highly compatible alternative to Microsoft Office, WPS Spreadsheet allows you to format, clean, and export your data seamlessly before uploading to SharePoint.

1. Open Data in WPS Spreadsheet: Launch WPS Office and open your Excel or CSV file containing the SharePoint data.
2. Customize Date Format: Highlight the date column, right-click to select 'Format Cells', and apply a Custom format that matches your SharePoint site's regional setting.
3. Save and Export: Save the document as an .xlsx file, ensuring format compatibility for a smooth SharePoint import.

Easily format dates and times to match SharePoint regional requirements.Fully compatible with Microsoft Excel formats (.xlsx, .csv).Free and lightweight with a familiar, easy-to-use interface.Seamless migration and integration with existing workflows.

microsoft office alternative - wps office

Frequently Asked Questions

Why does SharePoint change my date format when importing from Excel?

SharePoint applies its own site-level regional settings to imported data. If the date format in Excel differs from the SharePoint site's default format, it will try to convert it, which may result in validation errors or incorrect date displays.

How do I check my SharePoint site's regional settings?

To check the regional settings, click the gear icon (Settings) in SharePoint, go to 'Site information', then 'View all site settings', and select 'Regional settings' under the Site Administration section.

Can I use formulas to change the date format in SharePoint?

Yes, you can create a Calculated Column in your SharePoint list and use the TEXT function, such as =TEXT(Created, "DD/MM/YYYY"), to display the date as a text string in your preferred format.

Document & File Issues