Dates are inconsistent. Either as ISO 8601 or as string, integer

[noelflores]

Im trying to use the actions: List rows present in a table > Create Item into sharepoint

However the flow fails since the Dates are being converted into different formats.

The DateTime Format is already selected as "ISO 8601". All comes from Excel Business Online.

Date1: "44634"

Date2: "2022-03-14T00:00:00.000Z"

Date3: "44634"

Date4: "44634"

Date5: "2022-03-14T00:00:00.000Z"

Date6: "2022-03-14T00:00:00.000Z"

Is there a way to make it consistent? Or is there anything I should look at?
The end goal really is to be able to match to the Date Fields upon creating an item in a sharepoint list.

[ekarim2020]

Try:

(1) In Excel, select the Date column (e.g. StartDate in the example below) and make sure that the column type is set to Date. Save the file and close.

(2) Then in the List rows present in a table, make sure that ISO 8601 is set:

Try the above steps.

 

See also: Easily convert Excel Dates to usable Power Automate Dates

https://www.tachytelic.net/2020/11/convert-excel-dates-power-automate/

and Converting Excel Date Time Serial values with Power Automate

https://ryanmaclean365.com/2020/07/28/converting-excel-date-time-serial-values-with-power-automate/

addseconds('1899-12-30',int(formatnumber(mul(float(triggerBody()['text']),86400),'0')))

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[cbohlman]

I am having this same issue where one column is reading as an integer while all other date columns are reading in ISO 8601 format. I have tried formatting the excel file and even protecting it to ensure the cells are set to a date format but it still reads it as an Integer. I already have a complicated IF expression for the column to ensure it puts the data in the right column so don't want to add another expression in the Update Item.

Is there any way to get the date to be consistently read as ISO 8601? 

[ekarim2020]

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.

[cbohlman]

@ekarim2020 ,

Hello,

The problem I am having is that Power Automate is not consistently reading dates in the same format. For one file it will read one column in ISO and the other as an integer. I have the excel file templated so that all date columns have the same format and the sheet is protected so formatting cannot be changed from the template settings. Sometimes there is no issue, other files it throws this error. 

I tried using your workaround to force it to add the 'Z', however the input is an 'Object' so I cannot use it in a Select operation. The current 'Update Item' has a IF expression to determine the value if the input field is blank as dates are not always required during this upload.