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

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

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.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (1,158)