Invalid date format while importing from excel to sharepoint list

Question

Hello,

I am kind of new with sharepoints and now trying to import excel files to list as I am going to make a power automate list.

As I tried so I encountered some error which is
like this : Invalid date/time value. A date/time field contains invalid data. Please check the value and try again.

Previously I tried Importing with only 2 date columns (both as an end date columns), But I need my list to be more detailed so I added the start date as well in the excel. And while importing I got the error.

I've tried changing the date formats in Excel, also tried changing my regional setting. But the error still showed when I tried to input more than 2 date columns.

this is how the Excel Sheet Data looks like.

this is how the imported list looks like before the style changed to date & time.

Would like to seek for help.

Thanks in advance

Answer 1

Hi Tia_TK,

Thanks for using Microsoft products and posting in the community, I'm glad to offer help.

According to your description, I did the following test and it worked properly on my side, when I set the column type to 'Date and time', they were formatted correctly without any error.

 

In your case, would you mind re-creating a new spreadsheet to check the outcome, it would be helpful to determine whether it is a random mistake.

Besides, if the issue persists, please remove any sensitive information and upload the workbook to your personal OneDrive (not OneDrive for business) or other personal cloud storage services, then paste the sharing link here so that we can further investigate it. And please indicate at which step you encountered an error message while importing the workbook.

Looking forward to your feedback.

Best Regards,

Arck

Answer 2

Hi Arck,

Thank you for the feedback.

I've tried as suggested to make a new spreadsheet. But still can't import manually nor with power automate to sharepoint list with more than 2 date columns.

In Manual import process I could only set 2 columns into the date and time type, while i at least need 4 columns to seperate start date and end date.

In Power Automate process, I always encountered error which said something like this "he 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/VBOStartDate' is required to be of type 'String/date'. The runtime value '""' to be converted doesn't have the expected format 'String/date'.

While I believe that my date format in excel is already in String format.

I tried several ways including making an initialize variables, and compose using some formulas. But I still get an error here and there.

I also set the date & time format to ISO format in the flow.

This is my current flow right now.

I've tried making it simple to with only trigger - List rows present - Apply to each (create item).

But always getting an error except when I changed the column type in sharepoints from Date & time into Text.

Answer 3

Hi Tia_TK,

Thanks for the clarification.

Since the problem persists when trying to manually import from a new sheet, the problem could be caused by the site itself as there is nothing wrong on my side. In this case, would you mind creating a new site to check whether the same problem occurs?

Besides, since we mainly provide supports on general problems and issues when using SharePoint Online, building custom automate flow is beyond our support scope, we would like to recommend you post a new thread about Power Automate flow in the following forums, these are our dedicated platforms that can cope with the related questions and issues:

-      Microsoft Power Automate Community - Power Platform Community

-      Microsoft Q&A

We are willing to help you, but we are afraid of providing any incorrect information that may not benefit you, we sincerely hope you can get the problem solved with the help of experienced supports and community members. We also welcome any community members to share any idea about your situation if anyone has related experiences.

Your understanding will be highly appreciated.

Best Regards,

Arck