Type String Error

Like (0) Share

Report

Posted on 26 Mar 2023 17:54:28 by Phineas

5,132 Super User 2025 Season 2

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.

I have the same question (0)

Summarized by AI

AI generated content may be incorrect

Did you find this summary helpful?

All responses (13)

Answers (0)

Phineas 5,132 Super User 2025 Season 2 on 27 Mar 2023 at 14:19:01

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')

Was this reply helpful? Yes No
Nived_Nambiar 18,014 Super User 2025 Season 2 on 27 Mar 2023 at 05:02:09

Like (0)

Report

Re: Type String Error

So @Phineas

Did u meant you are not getting dynamic content to replace hardcoded value ?

Was this reply helpful? Yes No
Phineas 5,132 Super User 2025 Season 2 on 27 Mar 2023 at 04:55:21

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.

Was this reply helpful? Yes No
Nived_Nambiar 18,014 Super User 2025 Season 2 on 27 Mar 2023 at 04:45:00

Like (0)

Report

Re: Type String Error

@Phineas

Replace that hard-coded value with dynamic content 😊

Was this reply helpful? Yes No
Phineas 5,132 Super User 2025 Season 2 on 27 Mar 2023 at 04:43:55

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.

Was this reply helpful? Yes No
Nived_Nambiar 18,014 Super User 2025 Season 2 on 27 Mar 2023 at 02:36:51

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 🙂

Was this reply helpful? Yes No
Phineas 5,132 Super User 2025 Season 2 on 26 Mar 2023 at 23:01:33

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",

Was this reply helpful? Yes No
grantjenkins 11,059 Moderator on 26 Mar 2023 at 21:46:59

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

Was this reply helpful? Yes No
Phineas 5,132 Super User 2025 Season 2 on 26 Mar 2023 at 21:28:22

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.

Was this reply helpful? Yes No
Phineas 5,132 Super User 2025 Season 2 on 26 Mar 2023 at 19:56:53

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?

Was this reply helpful? Yes No