Date problem flow from excel to sharepoint

(0) Share

Report

Posted on by annemie

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

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (0)

v-yamao-msft on at

Like (2)

Report

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.

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

Please taka a try with it on your side.

Best regards,

Mabel

Was this reply helpful? Yes No
yellowsubmarine 62 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Cripp88 72 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Cripp88 72 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
RobElliott 10,323 Super User 2025 Season 2 on at

Like (2)

Report

@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.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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

Was this reply helpful? Yes No
Cripp88 72 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
timoshea 15 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Devin_ 3 on at

Like (0)

Report

Thank you so much for this!

Was this reply helpful? Yes No