Type String Error

[Phineas]

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.

[Nived_Nambiar]

Hi @Phineas 

The ticketDate is not appearing as date when flow is working , it is showing as number of days from 1900 Jan 1st [included]

So while adding the ticket date, make sure to convert the number of days to date like below

addDays('1899-12-31',items('Apply_to_each_2)?['TicketDate'])

Note: I hope thisitems('Apply_to_each_2)?['TicketDate'] the dynamic content corresponding to TicketDate, if not please add approapriate one 🙂

Hope it helps

Mark it as solution if it resolves your query

[Kaif_Siddique]

Try this:

addDays('1899-12-30', int(item()['TicketDate']), 'MM/dd/yyyy')

Or refer this for alternative and detailed information:

https://manueltgomes.com/microsoft/powerautomate/convert-excel-number-to-date/

Regards

Kaif

[Phineas]

Getting close.

The SP columns are now appearing as dates; However, they are the wrong dates. Something is getting "lost in translation". Though they are dates, they are not the same dates.

Also, the 'WaitTime' should show in hh:mm:ss, which is formulated in Excel column as 'OutTime' - 'InTime'. 

For instance, in the Excel the 'WaitTime' for the entry created in the SP List is '00:18.34', but SP is seeing as something else and showing '3/26/2023 5:00 AM'

New SP Item

Excel List -

[Phineas]

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?

[Phineas]

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.

[grantjenkins]

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]

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

[Nived_Nambiar]

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]

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]

@Phineas 

Replace that hard-coded value with dynamic content 😊

[Phineas]

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]

So @Phineas 

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

[Phineas]

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