ISO dates from Excel only working in test mode, not in Run mode

[LLinx]

Hi!

I'm having trouble getting a Date field from Excel to be returned in ISO format. While it works in "test" mode, it does not work in live/run mode.

Some backstory: I've created a flow to export data from one of our internal systems via an API, and write the resulting data to an Excel file. The API connector allows me to request data for a specific time period. To ensure I always request the necessary results, even if the flow would have failed once, I start my flow by reading the existing Excel file, getting the highest "Last Updated Date" and using that as the starting date range.

When extracting this data/Date from Excel, something strange is occuring. When I "test" the flow, it all works perfectly. The flow connects to the excel, gets the TOP 1 record, on Last Updated Date descending, and returns it in ISO mode.

The following Compose step gets the first() row from the excel and extracts the date from it, and formats it to the correct format I need.

formatDateTime(

first(outputs('List_rows_present_in_a_table')?['body/value'])['LastUpdatedDate'],

'yyyy-MM-dd hh:mm:ss')

When running this flow in "Test mode", it works perfectly.

Without doing any changes, if I run the same flow on the schedule, or clicking the "Run" button, it fails.

Unable to process template language expressions in action 'First_Date' inputs at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '45194.275' was not valid. The datetime string must match ISO 8601 format.'.

And indeed, the returned json from Excel returns the date as a number when in Run mode:

While it returns the date as ISO date in "Test" mode.

Has anyone else had this issue before? Any ideas on how to resolve this?

[wskinnermctc]

Thank you for the pictures, they are very helpful.

Do you have any blank rows or any text in the Excel column LastUpdatedDate? 

Power automate does a strange thing where it will read dates as ISO 8601 in an Excel column, but if there is a blank cell, it will read the remaining dates as Serial. 

Similar to the blank is if there is text. It is like the change of data will disrupt how PA reads data.

I don't know if this is your problem, but it is something to look at.

[LLinx]

Thanks for the response @wskinnermctc .

Unfortunately, that's not the issue: The excel column only contains datetime values, there's no text or blank cells in there.

It really seems related to "Test" vs "Run" and different behaviour from the Excel connector. I tried again today, without making any changes to the flow:

[LLinx]

I did some more troubleshooting for this topic.

Recreating the Excel action in the flow did not change the result and still gave the same issue.

Removing the Excel action, saving the flow, then readding the action did not change the result, still gave the same issue.

In a completely new flow, outside of a Solution, doing the starting 4 actions works perfectly. When I add a new flow with these same steps to my Solution, that flow does not work.

Outside of a solution, only the starting 4 actions work perfectly, also in "Run" mode:

Note: I did not copy-paste any of these steps, but recreated them from scratch.

However, after recreating the same steps in a new flow in the solution I was working in, it broke again. It looks like it's the combination of the Solution and the "Return dates as ISO" that is causing the issues, even when I configure the Excel connector in it's most simple form (without odata sort query & top parameters):

It's starting to look like this is an actual bug, not something on the user side that is configured incorrectly, so I probably need to get in touch with MS support?

(I did some very quick back-to-back Test => Run => Test for this, to show the flow is not edited 😊)

If anyone else would have an other idea, please do let me know.

[wskinnermctc]

I can't explain why there is a difference between test and run.

However, I know people can have issues when the Excel date is in dd-M-yyyy (European?) format. Once again, I don't have an explanation for that.

I guess you will just have to use the dates as Serial and then convert them into ISO within the flow. So set the option to Serial instead of ISO 8601 and convert all of them.

If you check part of my solution in this post about blank excel dates it shows how to convert serial to ISO 8601.

To convert the Serial dates to a format that can be used by SharePoint the expression addDays() or addSeconds() has to be used. There are many posts on converting dates that can be found. For my example I’m going to use the addSeconds() expression for both columns [‘Birth Date’] and [‘CSVReportLastUpdate’] since one column includes time and a bonus is the final result will be in a format that can be accepted by SharePoint without writing it out.

The expression addSeconds() is adding the number of seconds to the date 1899-12-30 based on the Serial date and then formats it. If no additional formatting such as yyyy-mm-dd is added to the addSeconds() expression it will default to a ISO 8601 format that is readable by SharePoint so I don’t add any additional formatting.

addSeconds('1899-12-30',int(formatNumber(mul(float(item()?['CSVReportLastUpdate']),86400),'0')))

Or you can probably find other posts or google convert Serial to ISO. 

[pecan008]

Thanks! It seems it might be root cause for my troubles however it seems no only blank cells are causing this but also cells with text instead of date.

Any clues how to solve this?

Peter