Import Excel to SP Column Data Error

(0) Share

Report

Posted on by jamescosten

643

For the love of god why is this so hard.

I have two date columns that are set to Short Date, I have gone in to text to columns and changed them to DMY format. All fine but when I go to import my excel sheet into Sharepoint it imports this as text format and doesnt recognise it as a date. The sharepoint column is a date column and I dont know why Microsfot cant just make this work...

Any help is appreciated.

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report

It would be helpful if you show your flow when you post a question, but I think I know what your problem is. To populate a date column, you must format the date as MM/dd/yyyy (as noted in the screen shot of a date field in SharePoint). If you have time turned on, then it needs to be formatted as MM/dd/yyyy hh:mm tt instead.

I don't think it matters, but I don't understand when you say the columns are set to short date. Is this something new Microsoft is introducing? I've been using SharePoint for about 15 years and the only options are date or date & time, and standard and friendly display format, with the display format having no effect on populating the field.

Was this reply helpful? Yes No
jamescosten 643 on at

Like (0)

Report

Apologies:

My Column is:

My Excel Column is:

The flow fails and the details are:

You can see the Last serviced date shows the text version of the date "45457".

Was this reply helpful? Yes No
Suggested answer

David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report

You don't show how the action that is failed is configured, so I don't know if you formatted the date as I said in my original post. However, based on the screen shot of your Excel data, that is not in the required format. In the Excel action, be sure to enable ISO8601 dates in the advanced section. Then I assume in the create item action you're using the formatDateTime action to format the date from Excel as MM/dd/yyyy.

Was this reply helpful? Yes No
jamescosten 643 on at

Like (0)

Report

Well the Crate Item is just configured as standard, Ive change nothing for the Create itme and I have the parameters as:

So its taking the data from the cell. The cell is configured as a date, the column is set to the date. I cant see anwywhere what I would need to change to get the date format. Both my excel and sharepoint are set to the same format.

Was this reply helpful? Yes No
David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report

Hopefully someone else will respond as I am not sure how to be clearer, but when you set the date in SharePoint, it MUST be in the MM/dd/yyyy format. Your spreadsheet is showing it is in dd/MM/yyyy format instead, and you are doing no conversion in the action to format it in the correct format. Unless we now have 14 months in a year, your Excel data is in day, month, year format instead of month, day, year.

Was this reply helpful? Yes No
jamescosten 643 on at

Like (0)

Report

Your assuming the sharepoint is set to american, which it is not. My regional settings show the date format of the column to be the same as my excel. Unless the system is only showing that format but in the code its always set to american.

Was this reply helpful? Yes No
David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report

I am not sure then. Our SharePoint tenant is in the EU and formatting the date as I have mentioned has always worked for me.

Was this reply helpful? Yes No