cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Flow error - dates pulled in from Excel Table

Hi,

My flow has been working without issue for around 3 months.  However around 7-10 days ago the flow continual fails. 

Unable to process template language expressions in action 'Compose' inputs at line '1' and column '36937': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

CrispinSteele_0-1611241545412.png

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

 

The issue causing the fail was in the apply to each - format date.  It appears that the "Date" pulled in from the excel table instead of the character based date (sorry don't know terminology e.g. 44472 etc.) it was pulling dates through as UTC (e.g. 2021-01-21...Z).

 

Therefore I simply changed the formatdateTime to the Date from Dynamic Content list.  This worked for 2 days and this morning the flow failed again.  The table date data is again being pulled through as 44472 or whatever it was.  Why?  I changed teh flow back to the original formatDateTime as above and it runs perfectly again.

 

The original table and the data within it hasn't changed format or content.  Does anyone know why from day to day the date format changes and what expression I can add to combat this.  For example is there an if date format is UTC then Dynamic Content, otherwise formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['Date']),-2)), 'yyyy-MM-dd')

 

Or any other work around.  I hope this is easy to solve.  Any suggestions much appreciated.

 

Cris

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User II
Dual Super User II

Hi @CrispinSteele ,

 

https://www.dontpapanic.com/blog/?p=681

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

View solution in original post

3 REPLIES 3
Dual Super User II
Dual Super User II

Hi @CrispinSteele ,

 

https://www.dontpapanic.com/blog/?p=681

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

View solution in original post

Hi Reza,

Thank you for the link.  So if I have read the blog correctly the excel for business connecter has been rolled back to pull in dates as Serial numbers - so in short if I revert back to the original expression formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['Date']),-2)), 'yyyy-MM-dd') - all will be good?

Hi @CrispinSteele ,

 

Yes please. Back to the serial format 🙂

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (28,250)