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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

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.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (4,928)