cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Date problem flow from excel to sharepoint

I have a flow that feeds a sharepoint list from an excel.

 

The problem : 

there is a date column in the excel, when I use the dynamic content it gives me this error

"status": 400,

  "message": "String was not recognized as a valid DateTime.\r\nclientRequestId: 8367bc4b-41aa-4e07-a747-f3b5af11fa45\r\nserviceRequestId: cb51a99e-606f-7000-6082-ad9b7ca62fc6"

 

So I used an expression to replace the dynamic content :

 

formatDateTime(items('Apply_to_each')?['AANSLUITING'],'ddmmyyyy')

 

But then It gives me this error : 

 

InvalidTemplate. Unable to process template language expressions in action 'Create_item' inputs at line '1' and column '2506': 'In function 'formatDateTime', the value provided for date time string '15/11/2018' was not valid. The datetime string must match ISO 8601 format.'

 

I now have a workaround where I put all dates to numbers and use the following expression

addDays(addDays(formatDateTime('1900-01-01T00:00:00'),int(items('Apply_to_each')?['AANSLUITING'])),-2)

then it works, but this is not what I want to tell the user that he has to change the dates to numbers each time he wants to upload the excel to the sharepoint list. In the end we even want to automate the flow that he picks the the excel from a mail or a specific place and then there is no person to change the excel before the flow picks it up

 

flow.jpg

9 REPLIES 9
Community Support
Community Support

Hi @annemie,

 

Thanks for feedback.

The issue could be reproduced by me.

I am afraid that it is current limitation of connector Excel online which works fine if you are working with Excel connector action Get rows.

I have figured out a workaround for it.

First, format the Number Format of the Date column as Text on Excel table, then enter the date into the column manually.

1.PNG

Then create the flow. The formatted date column will be recognized as valid DateTime.

2.PNG

Please taka a try with it on your side.

 

Best regards,

Mabel

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @annemie 

 

Have you tried checking the SharePoint Data list settings for the Data & Time column?

 

Although the excel identifies it as date but in SPO a different fromat is required.

 

Below is the link on how change and check you Data Time Format in SPO

https://www.c-sharpcorner.com/blogs/sharepoint-how-to-change-date-format-on-a-site-or-list

 

Regards,

ys

Helper I
Helper I

I've been having this issue. I have a Power BI report that formats data sources to the exact format needed by the Flow. Users with limited knowledge can export the report, convert the data to a table and save in a folder to be uploaded to SharePoint by Flow. Converting dates to text is a step I don't want them to have to take as it will lead to Flow failures when they inevitably forget to do it.

 

I got around this by adding an apostrophe at the start of the date format, e.g. COLUMN = FORMAT ( [Date] , "'yyyy/MM/dd" )

 

In Flow, where you need to use the Date, use an expression like this: substring ( [Date] , 1, 10 ) which will remove the leading apostrophe. No need to use formatDateTime.

 

Sorry to necro the thread but noticed it hadn't been answered and had just found a solution myself.

Hi,

 

I'm facing the exact same issue and dont want the end users to be confused about what date/text format to enter in that column. I tried replicating your solution but could not understand it fully. Could you please explain in detail what you meant, this would be really helpful of you!

 

Thanks!

Hi @jaykay96 

 

Sorry for the late reply. You need something that will make Excel interpret the date string as text and not convert it to 43882 or something like that.

 

It's not obvious in my initial message but there is a single quote before the date, like '18/02/2020. When exported from Power BI, this column will be interpreted as text by Excel, not dates.

 

In Flow, use a substring expression to remove the single quote, such as substring ( [date], 1 ). This will keep all characters after the first one.

 

Hope this helps!

@Cripp88  to convert a date from Excel you need to format it using a formatdateTime expression:

formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['Update']),-2)), 'dd/MM/yyyy')

where in my example 'Update' is the column in Excel.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

I figured out, its better to use the formula- =TEXT(D2,"MM/DD/YYYY") to convert the format of a cell from 'Date' to text. Then use the text cell as an input in PowerAutomate Flow

@jaykay96 

 

Excellent!

 

Just make sure that you produce expected results in multiple scenarios. Excel will interpret text output as dates if it receives text in a valid date format I believe. This may work differently for dates that make sense in US format, UK format, or both.

I have the same issue. Converted the excel data to text and it worked, but I have UK dates (dd/mm/yyyy) while they have come in to SPO as US dates (mm/dd/yyyy)

 

Is there anyway I can change the region setting on the SPO, as my spreadsheet will use UK dates only.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Top Kudoed Authors
Users online (7,139)