Hi @cbohlman ,
I can see a similar issue in one of my Excel files. I don't have a solution yet, but I suspect it is a data entry issue.
Here is an extract from the Excel file showing the column "Date swapped". I used the IsNumber function to validate that the date values are numbers. It failed on the following three values:
Please see the section Validate the Excel data first, in Easily convert Excel Dates to usable Power Automate Dates (tachytelic.net).
The entire column is of date type:
I switched the entire column to General (I could have used Number):
This is what my demo flow read for "Date swapped" 24/05/2022 - a string and not a date:
{
"Date Swapped": "24/05/2022",
"Email Sent": "2022-04-28T00:00:00.000Z",
"Date Booked": "2022-05-24T00:00:00.000Z",
"IsNumber": "False"
},
{
"Date Swapped": "24/05/2022",
"Email Sent": "2022-04-28T00:00:00.000Z",
"Date Booked": "2022-05-24T00:00:00.000Z",
"IsNumber": "False"
},
{
"Date Swapped": "24/05/2022",
"Email Sent": "2022-04-28T00:00:00.000Z",
"Date Booked": "2022-05-24T00:00:00.000Z",
"IsNumber": "False"
},
Here is something you could try as a work around.
The date times we read in from Excel have a Z character at the end.
{
"Date Swapped": "24/05/2022",
"Email Sent": "2022-04-28T00:00:00.000Z",
"Date Booked": "2022-05-24T00:00:00.000Z",
"IsNumber": "False"
}
This is means that dates are read as UTC format (time zone neutral format). Please see this excellent post by @Pstork1 : Excel Date changes in Power Automate - Power Platform Community (microsoft.com).
which explains why.
We could try the following expression to format those problematic dates:
if(endsWith(item()?['Date swapped'],'Z'), item()?['Date swapped'], parseDateTime(item()?['Date swapped'], 'en-GB') )
And when run we get the following:
{
"Date Swapped": "2022-05-24T00:00:00.0000000",
"Email Sent": "2022-04-28T00:00:00.000Z",
"Date Booked": "2022-05-24T00:00:00.000Z",
"IsNumber": "False"
},
{
"Date Swapped": "2022-05-24T00:00:00.0000000",
"Email Sent": "2022-04-28T00:00:00.000Z",
"Date Booked": "2022-05-24T00:00:00.000Z",
"IsNumber": "False"
},
{
"Date Swapped": "2022-05-24T00:00:00.0000000",
"Email Sent": "2022-04-28T00:00:00.000Z",
"Date Booked": "2022-05-24T00:00:00.000Z",
"IsNumber": "False"
},
The "Date Swapped" field doesn't have the timezone set as UTC - there's no Z character at the end of the date. As the dates I am reading from Excel do not have time stamps (I only need the date) we could append a character Z to the end of it:
if(endsWith(item()?['Date swapped'],'Z'), item()?['Date swapped'], concat(parseDateTime(item()?['Date swapped'], 'en-GB'),'Z') )
And when run that expression we get the following:
{
"Date Swapped": "2022-05-24T00:00:00.0000000Z",
"Email Sent": "2022-04-28T00:00:00.000Z",
"Date Booked": "2022-05-24T00:00:00.000Z",
"IsNumber": "False"
},
{
"Date Swapped": "2022-05-24T00:00:00.0000000Z",
"Email Sent": "2022-04-28T00:00:00.000Z",
"Date Booked": "2022-05-24T00:00:00.000Z",
"IsNumber": "False"
},
{
"Date Swapped": "2022-05-24T00:00:00.0000000Z",
"Email Sent": "2022-04-28T00:00:00.000Z",
"Date Booked": "2022-05-24T00:00:00.000Z",
"IsNumber": "False"
}
Or we could convert it to UTC using convertToUtc function. Find what works best for you.
But I think the real solution is to fix the source data, and to be able to read valid dates from Excel. But the flow may be able to handle your exceptions.
Hope this helps.
Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.
