cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

Hi, I have a flow that is connected to excel and uses the values to populate a sharepoint list. It is returning the date value as a number. I have read other post about this issue and input the following formula in the date values for the create item part of the flow: 

addDays('1899-12-30',int(items('Apply_to_each_2')?['Date']))
 
Yet, I am still getting an error, i also tried replacing 'int' with 'float' and i get the same error. The dates appears in excel as the following 'mm/dd/yyyy'. 
 
I also remade the flow to no avail. 
I am not sure what else could be the issue, any suggestions? 
11 REPLIES 11
askondo
Community Support
Community Support

Hello,

There are two ways to make it possible.

Option A. Change the type of the column which store the date to "string" format.

  (You need to keep the input contents of date format the same way as well.)

1. Copy the area which you would like to change from date to string.

2. Paste the copied cell to a text editor. The cell data will be shown as text format separated by tab.

3. From a text editor, select all and make a copy.

(Ctrl + A ⇒ Ctrl + C)

4. Go back to Excel, and change the original cell's format to sting by set dialog.

   The original date will be shown as number, no need to worry about it.

   When you overwrite the original cells by pasting the text data (Ctrl + V), the date will be entered as string in the cell.

Option B. With the text function, the date should be converted to a string format and store it in another cell,

and retrieves the value of the converted cell on the flow.

1. Add a column to the Excel table to store string dates. In the cell value, type as follows.

 "=TEXT([Date format to be retrieved],[Format you want to retrieve])"

 e.g. =TEXT(I2,"yyyy/mm/dd hh:mm:ss")

  

2. By flow, the created column in 1 will be set in SharePoint date columns with dynamic contents of Excel.

Anonymous
Not applicable

For option A, steps 1-3 are you referring to changing the SharePoint column type ?

ShriramP
Advocate I
Advocate I

I understand this is already late but might help others looking for solution to same problem. you can try with this formula:

 

if(empty(items('Apply_to_each_2')?['Date']),null,addDays('1899-12-30',int(items('Apply_to_each_2')?['Date']),'yyyy-MM-dd'))

BrownandBrown
Regular Visitor

Thank you @ShriramP 

Our flows stopped working correctly yesterday. Your post helped get them running again.

 

Original.

formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['TermDate']),-2)), 'MM/dd/yyyy')

 

Changed.

if(empty(items('Apply_to_each')?['TermDate']),null,addDays('1899-12-30',int(items('Apply_to_each')?['TermDate']),'MM/dd/yyyy'))

 

Works as expected.

@BrownandBrown that is good to know! 🙂

Hi @ShriramP ,thank you very much. I was desperately looking for a solution and fortunately came across your response.😀👍 

Many thanks @ShriramP.   Could I pick your brains a little further please:   I have been trying a couple of days to make a flow work to import rows of Excel (4 fields: ID, App Name, App Description and a Date) into a flow through to update a sharepoint list with the same fields.   I have been copying the excel date to a variable before testing the row ID with the sharepoint list ID via a condition.   If the ID already exists, then perform an Apply to each Update Item otherwise Create Item. 

 

However, although the flow completes without error the Date field is not being updated or created.    The date number coming from Excel is appearing in the variable but I think Automate is treating it as null and not doing anything.   The update/create expression I have used is:

if(empty(items('Apply_to_each')?['varDate1']),null,addDays('1899-12-30',int(items('Apply_to_each')?['varDate1']),'yyyy-MM-dd')).

 

     

@omelo  Great! 🙂

Hi @surfpaddy is "varDate1" is correct Excel column name? or maybe in your case you can use "Date" instead?

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Users online (1,474)