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

Compare excel data with actual date

I have a list of all upcoming Teams live events with date and time within an excel sheet.
Excel links.png

The idea is to compare the current date with the ones in the excel sheet and if there is a scheduled meeting today the shortURL should be changed to the corresponding URL stated in the excel.

 

The changes for the shortURL works via a REST API perfectly. But unfortunately I'm not able to figure out the Excel part.

I'm able to list the rows present in a table and also to convert them into date/time format, but I'm not able to compare them with the actual date.

I guess it's pretty easy to accomplish for a pro.

Thanks in advance.

 

Screenshot Power Automate.png

8 REPLIES 8
fchopo
Super User II
Super User II

Hello @thomschi 

Could you share the expression you are using to convert the datetime from excel? You should take into account that the utcNow() function (used in the today variable initialization) will return day plus time, so you may format it using this expression:

formatDateTime(utcNow(),'MM/dd/yyyy')

Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

My question is how to compare the actual date with the value of Date within the Excel Data Object.

The way I've created it, it's not working.

 

Error message


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



Hello @thomschi 

 

The dates in Excel are stored as an integer value, that is the number of days since 12/31/1899, so you could the following expression:

addDays('1899-12-31',int(item()?['Date']))

Have a look at this post: https://www.shanebart.com/ms-flow-excel-dates/

Hope it helps!

Ferran 

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

Please read my question. Date conversion is not my problem.

 

2020-09-17 14_12_05-Run History _ Power Automate and 5 more pages - ServiceAccount - Microsoft​ Edge.png

@thomschi 

Can you share the expression you are currently using inside your Condition rule?

Also, is your Date excel column formated as Date or as Text?

 

@fchopo is one of the most active members in this community, his two suggestions are related to my second question and are IMHO good insights.

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



That's my expression within the condition rule

 

addDays('1899-12-30', int(item()?['Date']), 'yyyy-MM-dd')

 

And the today variable is this function

 

utcNow('yyyy-MM-dd')

 

And the error message is this

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

 

Can anyone help me out here please.

Thanks in advance.

Hi!

My suggestion is to add a dummy 'Compose' action block inside the 'Apply to each', just before the 'Condition' actual block, assign as its input WDL expression just for troubleshooting purposes

item()?['Date']

 

REexecute the flow, share 'Compose' value for the iteration that throws the error.

THanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (43,649)