Unanswered

Type String Error

(0) Share

Report

Posted on by Phineas

5,049 Super User 2025 Season 1

Can anyone assist me with resolving this issue?

The Excel columns are the following. There are four date columns. The error is on 'TicketDate' (not shown), it is a basic DateTime column with (ea 10/14/2022) as content.

All responses (13)

Answers (0)

Phineas 5,049 Super User 2025 Season 1 on at

Like (0)

Report

Re: Type String Error

The overarching issue is my inability to convert the DateTime content from Excel, through Power Automate and into the SP List properly.

The Excel columns -
Date (looks like 10/1/2023)
Time (looks 10/1/2023 7:16 AM)
Out Time looks 10/1/2023 7:35 AM)
Wait Time (looks like 00:18:13 and it the difference between the 'OutTIme' and 'InTime' using =IF(TRIM(F2)="","",F2-D2)

When I use any of the following formulas I get an 'int' invoke error message in Power Automate, or I get a conversion that does match the actual times or dates (all of which are shown in my earlier posts above).

TicketDate:
addDays('1899-12-31',int(first(split(variables('varTicketDate'),'.'))),'yyyy-MM-dd')

InTime:
addDays('1899-12-31',int(first(split(variables('varInTimeDate'),'.'))),'yyyy-MM-ddThh:mm:ss')

OutTime:
addDays('1899-12-31',int(first(split(variables('varOutTimeDate'),'.'))),'yyyy-MM-dd')

WaitTime:
addseconds('1899-12-31', int(formatNumber(mul(float(item()?['WaitTime']),86400),'0','en-us')),'HH:mm:ss')
Nived_Nambiar 17,895 Super User 2025 Season 1 on at

Like (0)

Report

Re: Type String Error

So @Phineas

Did u meant you are not getting dynamic content to replace hardcoded value ?
Phineas 5,049 Super User 2025 Season 1 on at

Like (0)

Report

Re: Type String Error

That had been the issue all along; None of the dynamic content coding options have delivered the desired output to the SP List.

The closest I have gotten is the output to an array in Power Automate.

Once the data is in SP its formatted differently than what is shown in Excel or the array.
Nived_Nambiar 17,895 Super User 2025 Season 1 on at

Like (0)

Report

Re: Type String Error

@Phineas

Replace that hard-coded value with dynamic content 😊
Phineas 5,049 Super User 2025 Season 1 on at

Like (0)

Report

Re: Type String Error

Would that be a 'hard-coded' solution?

Meaning it would only work if the date and time in the column is actually 2022-10-01T07:16:54.000Z?

What if the date and time are different, dynamic results is what is needed/required.
Nived_Nambiar 17,895 Super User 2025 Season 1 on at

Like (0)

Report

Re: Type String Error

Hi @Phineas

Try to format the date like this

formatDateTime('2022-10-01T07:16:54.000Z,'yyyy-MM-dd hh:mm:ss')
This would give results : 2022-10-01 07:16:54

Hope it helps 🙂
Phineas 5,049 Super User 2025 Season 1 on at

Like (0)

Report

Re: Type String Error

From an array installed below the List rows present in table. This is the first item in the SP List.

There appear to be a time zone conversion issue alone with the translation issue.
In Excel the content is:
Date: 10/1/2022
In Date: 10/1/2022 7:16:54 AM
Out Date: 10/1/2022 7:35:07 AM
Wait Time: 00:18:13
Total Cost is currency in Excel but translated as date in PA and SharePoint.
"Ticket _x0023_": "S1198604",
    "Date": "2022-10-01T00:00:00.000Z",
      "In": "2022-10-01T07:16:54.000Z",
     "Out": "2022-10-01T07:35:07.000Z",
     "WaitTime": "1899-12-30T00:18:13.000Z",
  "Total Cost": "1900-04-24T08:09:36.000Z",
grantjenkins 11,059 Super User 2025 Season 1 on at

Like (0)

Report

Re: Type String Error

In your List rows present in a table (if you haven't already), can you set the DateTime Format to ISO 8601 then run your flow and have a look at the output from List rows present in a table to see if they're now returning as valid dates (not the serial date that you are currently getting).
Phineas 5,049 Super User 2025 Season 1 on at

Like (0)

Report

Re: Type String Error

I've gotten the output to the following in Power Automate.

The three still do not match. The output to and display in the SP List should be:

Date: Date (ea 10/1/2022)
In: Time (only) 7:16 (w or w/o AM)
Out: Time (only) 7:35 (w or w/o AM)
WaitTime: Time (only, in hh:mm:ss) 00:18:13

Source data in Excel:

Power Automate Output (using your expression recommendations) -
TicketDate:
addDays('1899-12-31',int(first(split(variables('varTicketDate'),'.'))),'yyyy-MM-dd')

InTime:
addDays('1899-12-31',int(first(split(variables('varInTimeDate'),'.'))),'yyyy-MM-ddThh:mm:ss')

OutTime:
addDays('1899-12-31',int(first(split(variables('varOutTimeDate'),'.'))),'yyyy-MM-dd')

WaitTime:
addseconds('1899-12-31', int(formatNumber(mul(float(item()?['WaitTime']),86400),'0','en-us')),'HH:mm:ss')

SP List - 'InTime' and 'OutTime' should only show the time shown in the DateTime columns of the Excel, and 'WaitTime' should show hh:mm:ss from Excel.
Phineas 5,049 Super User 2025 Season 1 on at

Like (0)

Report

Re: Type String Error

Please see following up regarding wrong dates displaying.

Also, there are times when there is NO content in the 'OutTime' date column, which I think may be causing some of the errors.

How do I account for this in the formula?