cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CrispinSteele
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
RezaDorrani
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
RezaDorrani
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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,022)